JSON_ARRAYAGG (Transact-SQL)
Applies to:
Azure SQL Database
Azure SQL Managed Instance
Constructs a JSON array from an aggregation of SQL data or columns.
To create a JSON object from an aggregate instead, use JSON_OBJECTAGG.
Note
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). .
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
object_id | column_list |
---|---|
3 | ["rsid","rscolid","hbcolid","rcmodified","ti","cid","ordkey","maxinrowlen","status","offset","nullbit","bitpos","colguid","ordlock"] |
5 | ["rowsetid","ownertype","idmajor","idminor","numpart","status","fgidfs","rcrows","cmprlevel","fillfact","maxnullbit","maxleaf","maxint","minleaf","minint","rsguid","lockres","scope_id"] |
6 | ["id","subid","partid","version","segid","cloneid","rowsetid","dbfragid","status"] |
7 | ["auid","type","ownerid","status","fgid","pgfirst","pgroot","pgfirstiam","pcused","pcdata","pcreserved"] |
8 | ["status","fileid","name","filename"] |