New JSON Functions (JSON_ARRAY and JSON_OBJECT) are here to help you construct JSON documents dynamically in SQL SERVER

     JSON Functions are introduced to SQL Server in version 2016. Saving JSON documents and retrieving documents using JSON Functions brings many possibilities to SQL Server. It is great to see that Microsoft continues to add different functions to the original JSON functions set.

    Today, I will explain two new JSON functions which are available in SQL Server 2022 and Azure SQL Database. 

JSON_ARRAY()

     Original JSON Functions were about retrieving the saved JSON documents. There was not an easy way to construct a new JSON document or property type. You can use JSON_ARRAY() function to construct JSON Arrays. Without this function, if you try to add an array to JSON in SQL Server, you might end up with a string without square brackets. You can control what to do if any values are NULL too.

     In the following example, I create an array by passing a couple of numbers

    SELECT JSON_ARRAY(70, 81, 74, 88)    

You control to include or exclude NULL values by using ABSENT ON NULL or NULL ON NULL clauses. By default, NULL will not be included in the array.

    SELECT JSON_ARRAY(70, 81, NULL, 74, 88 ABSENT ON NULL)

    SELECT JSON_ARRAY(70, 81, NULL, 74, 88 NULL ON NULL)

     To construct a JSON object text, you need to use JSON_OBJECT() function. This function take one or many json key values and constructs a JSON document. 

    SELECT JSON_OBJECT('Name':'Test Customer', 'Age':29)

    You can use ABSENT ON NULL or NULL on NULL clause to control how to handle NULL values.

    SELECT JSON_OBJECT('Name':'Test Customer', 'Age':29, 'Address':NULL ABSENT ON NULL)

   You can pass another JSON_OBJECT function or JSON_ARRAY as key value parameter.

    SELECT JSON_OBJECT('Name':'Room 1', 'Temps':JSON_ARRAY(70, 72, 74))