In SQL Server, we can use the JSON_ARRAY() function to construct JSON array text from zero or more expressions.

The resulting array contains the values we provide as arguments. Providing zero expressions results in an empty array.

The JSON_ARRAY() function was introduced in SQL Server 2022.

Syntax

The syntax goes like this:

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )  

<json_array_value> ::= value_expression

<json_null_clause> ::=
	  NULL ON NULL
	| ABSENT ON NULL

The following examples demonstrate how it works.

Example

Here’s a basic example:

SELECT JSON_ARRAY( 1, 2, 3 );

Result:

[1,2,3]

In this case I created a basic JSON array with three elements.

Create an Empty Array

We can call JSON_ARRAY() without any arguments to create an empty JSON array:

SELECT JSON_ARRAY();

Result:

[]

The ABSENT ON NULL Option

By default, if any of the values we pass to JSON_ARRAY() is NULL, then that value is omitted from the resulting JSON array:

SELECT JSON_ARRAY( 1, NULL, 3 );

Result:

[1,3]

However, we also have the option of adding the ABSENT ON NULL argument to explicitly specify this:

SELECT JSON_ARRAY( 1, NULL, 3 ABSENT ON NULL);

Result:

[1,3] 

Same result.

The NULL ON NULL Option

We can alternatively specify NULL ON NULL to include the NULL value in the resulting JSON array:

SELECT JSON_ARRAY( 1, NULL, 3 NULL ON NULL);

Result:

[1,null,3]

Embed a JSON Object

We can include the JSON_OBJECT() function in order to create a JSON object:

SELECT JSON_ARRAY(
    1, 
    JSON_OBJECT('name' : 'Wag', 'weight': 10 ),
    2,
    3
  );

Result:

[1,{"name":"Wag","weight":10},2,3] 

Like JSON_ARRAY(), the JSON_OBJECT() function was first introduced in SQL Server 2022.

Add a JSON Array

We can pass another JSON_ARRAY() as an argument to JSON_ARRAY() itself in order to embed a JSON array into the resulting JSON array:

SELECT JSON_ARRAY(
    1, 
    JSON_ARRAY( 1, 2, 3 ),
    2,
    3
  );

Result:

[1,[1,2,3],2,3]

A Database Example

This example uses data from a database to provide multiple rows of JSON arrays:

SELECT 
  JSON_ARRAY( DeclarationDate, PayableDate, Amount )
FROM Dividends;

Result:

["2023-03-08","2023-04-03",10.5200]                                                                                                                                                                                                                             
["2023-06-08","2023-07-03",10.5200]                                                                                                                                                                                                                             
["2023-09-08","2023-10-03",10.5200]                                                                                                                                                                                                                             
["2023-12-08","2024-01-03",12.8600]

In this case I returned data from a table called Dividends. This table contains a column called DeclarationDate, a column called PayableDate, and another one called Amount. The table contains four rows, and so the example resulted in four rows being returned.

Here’s a similar example, except this time the first two columns are placed in a JSON object:

SELECT 
  JSON_ARRAY( 
    JSON_OBJECT( 'Declared' : DeclarationDate, 'Paid' : PayableDate), 
    Amount 
  )
FROM Dividends;

Result:

[{"Declared":"2023-03-08","Paid":"2023-04-03"},10.5200]                                                                                                                                                                                                         
[{"Declared":"2023-06-08","Paid":"2023-07-03"},10.5200]                                                                                                                                                                                                         
[{"Declared":"2023-09-08","Paid":"2023-10-03"},10.5200]                                                                                                                                                                                                         
[{"Declared":"2023-12-08","Paid":"2024-01-03"},12.8600]