Menu

SQL Server JSON_ARRAYAGG(): Aggregate Rows into a JSON Array

JSON_ARRAYAGG(value_expression) aggregates values from multiple rows into one JSON array. It can return one array for the whole input or one array for each GROUP BY group.

Availability: Microsoft currently lists JSON_ARRAYAGG as a preview feature in SQL Server 2025 (17.x). It is generally available in Azure SQL Database, Azure SQL Managed Instance when using the SQL Server 2025 or Always-up-to-date update policy, SQL database in Microsoft Fabric, and Fabric Data Warehouse. Check the current Microsoft function reference for platform and update-policy details before relying on it in production.

Syntax

JSON_ARRAYAGG(value_expression [ORDER BY order_expression [, ...n]]
              [NULL ON NULL | ABSENT ON NULL]
              [RETURNING json])

The default result is JSON text of type nvarchar(max). On platforms with the native json type, RETURNING json returns that type.

Aggregate values by group

Use GROUP BY to construct one array for each group. Put ORDER BY inside JSON_ARRAYAGG() when the element sequence matters; an outer ORDER BY only sorts result rows.

WITH Staff AS (
    SELECT *
    FROM (VALUES
        ('Engineering', 'Lin', 2),
        ('Engineering', 'Ada', 1),
        ('Support', 'Mia', 1)
    ) AS v(department, employee, display_order)
)
SELECT
    department,
    JSON_ARRAYAGG(employee ORDER BY display_order) AS employees
FROM Staff
GROUP BY department
ORDER BY department;

department  | employees
------------|----------------
Engineering | ["Ada","Lin"]
Support     | ["Mia"]

The outer ORDER BY department makes the group rows display consistently. The ORDER BY display_order inside the aggregate determines each array’s element order.

Handle SQL NULL values

By default, ABSENT ON NULL omits rows whose value expression is SQL NULL. Use NULL ON NULL to include those positions as JSON null:

WITH ValuesToAggregate AS (
    SELECT * FROM (VALUES (1, N'a'), (2, CAST(NULL AS nvarchar(10))), (3, N'b')) AS v(id, value)
)
SELECT
    JSON_ARRAYAGG(value ORDER BY id) AS omit_null,
    JSON_ARRAYAGG(value ORDER BY id NULL ON NULL) AS keep_json_null
FROM ValuesToAggregate;

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

Choose deliberately: omitting a SQL NULL removes an element, while emitting JSON null preserves its position in the array.

Return a native json result

If the target platform supports the native JSON data type, add RETURNING json to return that type instead of nvarchar(max):

SELECT JSON_ARRAYAGG(v.employee ORDER BY v.display_order RETURNING json)
FROM (VALUES ('Lin', 2), ('Ada', 1)) AS v(employee, display_order);

To build an array from expressions in a single row, use JSON_ARRAY(). To aggregate key/value pairs into one object, use Microsoft’s JSON_OBJECTAGG(). Browse the SQL Server JSON function guide for the full task-based list.

Advertisement