Menu

SQL Server JSON_ARRAY(): Build a JSON Array

JSON_ARRAY(value, ...) constructs a JSON array from zero or more SQL expressions. It was introduced in SQL Server 2022 (16.x) and is available on supported Azure SQL and Microsoft Fabric platforms. See Microsoft’s JSON_ARRAY reference for the current platform list.

Syntax

JSON_ARRAY([value [, ...n]] [NULL ON NULL | ABSENT ON NULL] [RETURNING json])

The function returns JSON text as nvarchar(max) by default. SQL Server versions and services that support the native json type can use RETURNING json to return that type. SQL Server 2022 doesn’t have the native json type; it is available in SQL Server 2025 (17.x) and selected Azure SQL and Fabric platforms.

Build an array from SQL values

Each expression becomes one JSON array element. SQL Server converts SQL data types using the same mapping rules as FOR JSON:

SELECT JSON_ARRAY('ready', 17, 2.5) AS payload;

payload
--------------------
["ready",17,2.5]

With no expressions, the function returns an empty array:

SELECT JSON_ARRAY() AS empty_array;

empty_array
-----------
[]

Choose how SQL NULL values appear

The default ABSENT ON NULL omits SQL NULL elements. Use NULL ON NULL to keep the position as JSON null:

SELECT
    JSON_ARRAY('a', NULL, 'b') AS omit_null,
    JSON_ARRAY('a', NULL, 'b' NULL ON NULL) AS keep_json_null;

omit_null   | keep_json_null
------------|-----------------
["a","b"] | ["a",null,"b"]

For arrays where element positions have meaning, this choice matters: omitting a value shifts later elements to lower indexes, while JSON null preserves the position.

Nest JSON objects and arrays

Use JSON_OBJECT() or another JSON_ARRAY() call to produce nested JSON values:

SELECT JSON_ARRAY(
    'A-1',
    JSON_OBJECT('sku':'A-1', 'quantity':2),
    JSON_ARRAY(1, 2, 3)
) AS payload;

payload
-------------------------------------------------------------
["A-1",{"sku":"A-1","quantity":2},[1,2,3]]

An ordinary SQL string that contains JSON text is serialized as a JSON string element. Use JSON constructors when you need nested arrays or objects.

Construct one array per row or aggregate rows

JSON_ARRAY() builds one value from expressions in the current row; it doesn’t combine multiple rows. To aggregate row values into one JSON array, use JSON_ARRAYAGG() on platforms that support the aggregate. Microsoft documents JSON_ARRAYAGG() for SQL Server 2025 (17.x) and supported Azure SQL and Fabric services.

For JSON objects, see Microsoft’s JSON_OBJECT() reference. For query results, see FOR JSON. Browse the SQL Server JSON function guide for the full task-based list.

Advertisement