This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Constructs a JSON array from an aggregation of SQL data or columns.

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

Both json aggregate functions JSON_OBJECTAGG and JSON_ARRAYAGG are currently in preview for Azure SQL Database and Azure SQL Managed Instance (configured with the Always-up-to-date update policy ). .

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
object_id
column_list
["rsid","rscolid","hbcolid","rcmodified","ti","cid","ordkey","maxinrowlen","status","offset","nullbit","bitpos","colguid","ordlock"]
["rowsetid","ownertype","idmajor","idminor","numpart","status","fgidfs","rcrows","cmprlevel","fillfact","maxnullbit","maxleaf","maxint","minleaf","minint","rsguid","lockres","scope_id"]
["id","subid","partid","version","segid","cloneid","rowsetid","dbfragid","status"]
["auid","type","ownerid","status","fgid","pgfirst","pgroot","pgfirstiam","pcused","pcdata","pcreserved"]
["status","fileid","name","filename"]
Related content
JSON Path Expressions (SQL Server)
JSON data in SQL Server
JSON_OBJECTAGG (Transact-SQL)