JSON_ARRAYAGG (Transact-SQL)

Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric Preview

Constructs a JSON array from an aggregation of SQL data or columns. JSON_ARRAYAGG can also be used in a SELECT statement with GROUP BY GROUPING SETS clause.

To create a JSON object from an aggregate instead, use JSON_OBJECTAGG.

Transact-SQL syntax conventions

Syntax

JSON_ARRAYAGG (value_expression [ order_by_clause ] [ json_null_clause ] )

json_null_clause ::=  NULL ON NULL | ABSENT ON NULL

order_by_clause ::= ORDER BY <column_list>

Arguments

value_expression

The value expression can be a column or expression in a query or constants/literals.

json_null_clause

Optional. json_null_clause can be used to control the behavior of JSON_ARRAYAGG function when value_expression is NULL. The option NULL ON NULL converts the SQL NULL value into a JSON null value when generating the value of the element in the JSON array. The option ABSENT ON NULL omits the element in the JSON array if the value is NULL. If omitted, ABSENT ON NULL is default.

order_by_clause

Optional. The order of elements in the resulting JSON array can be specified to order the input rows to the aggregate.

Examples

Example 1

The following example returns an empty JSON array.

SELECT JSON_ARRAYAGG(NULL);

Result

[]

Example 2

The following example constructs a JSON array with three elements from a result set.

SELECT JSON_ARRAYAGG(c1)
FROM (VALUES ('c'), ('b'), ('a')) AS t(c1);

Result

["c","b","a"]

Example 3

The following example constructs a JSON array with three elements ordered by the value of the column.

SELECT JSON_ARRAYAGG( c1 ORDER BY c1)
FROM (
    VALUES ('c'), ('b'), ('a')
) AS t(c1);

Result

["a","b","c"]

Example 4

The following example returns a result with two columns. The first column contains the object_id value. The second column contains a JSON array containing the names of the columns. The columns in the JSON array are ordered based on the column_id value.

SELECT TOP(5) c.object_id, JSON_ARRAYAGG(c.name ORDER BY c.column_id) AS column_list
FROM sys.columns AS c
GROUP BY c.object_id;

Result

Example 5

The following example returns a result with four columns from a SELECT statement containing SUM and JSON_ARRAYAGG aggregates with GROUP BY GROUPING SETS. The first two columns return the id and type column value. The third column total_amount returns the value of SUM aggregate on the amount column. The fourth column json_total_amount returns the value of JSON_ARRAYAGG aggregate on the amount column.

WITH T
AS (SELECT *
    FROM (VALUES (1, 'k1', 'a', 2), (1, 'k2', 'b', 3), (1, 'k3', 'b', 4), (2, 'j1', 'd', 7), (2, 'j2', 'd', 9)) AS b(id, name, type, amount))
SELECT id,
       type,
       SUM(amount) AS total_amount,
       JSON_ARRAYAGG(amount) AS json_total_amount
FROM T
GROUP BY GROUPING SETS((id), (type), (id, type), ());

Result

Related content

• JSON Path Expressions
• JSON data in SQL Server
• JSON_OBJECTAGG (Transact-SQL)