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’s current function reference lists JSON_ARRAYAGG for SQL Server 2025 (17.x), Azure SQL Database, Azure SQL Managed Instance, SQL analytics endpoint and Warehouse in Microsoft Fabric, and SQL database in Microsoft Fabric. The function also appears in SQL Server 2025’s What’s New list. The release notes say that What’s New features not in the preview table follow the SQL Server 2025 release status; JSON_ARRAYAGG is not listed in that table. Together, those pages imply that the function follows SQL Server 2025’s general availability status, although its function page doesn’t explicitly label the status. Check the function page’s Applies to list and the target service’s update policy before use.

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.