JSON_ARRAYAGG
JSON_ARRAYAGG constructs a JSON array as an aggregation of values from table rows.
JSON_ARRAYAGG (
value
ORDER BY sort_list
[NULL | ABSENT ON NULL]
[RETURNING datatype]
)
value
Specifies a constant, table column, or expression to be aggregated.
ORDER BY
Causes the items in the aggregate to be ordered in the output array. If value is a JSON_OBJECT or JSON_ARRAY constructor, the ORDER BY columns can include columns used by the JSON_OBJECT or JSON_ARRAY queries.
NULL ON NULL | ABSENT ON NULL
Specifies how nulls are handled. NULL ON NULL inserts nulls as SQL nulls. ABSENT ON NULL omits null values. Default is ABSENT ON NULL.
RETURNING datatype
Specifies the data type of the result. Datatype can be any string data type. Default is varchar(4000).
Examples
The query:
SELECT JSON_ARRAYAGG (unit) FROM emp;
returns: ["1A", "2A", "2A", "2B"]
SELECT JSON_OBJECT (
'row':emp.row,
'units':JSON_ARRAYAGG (
JSON_OBJECTAGG (emp.name VALUE emp.unit ABSENT ON NULL))
)
FROM emp
GROUP BY emp.row
returns two rows:
{"row" : 1, "units" : [{"Frank":"1A", "Bob:"1B"}]}
{"row" : 2, "units" : [{"Steve":"2A"}]}
The query:
SELECT JSON_OBJECT (
'row':emp.row,
'units':JSON_ARRAYAGG (
JSON_OBJECT (emp.name VALUE emp.unit ABSENT ON NULL) ORDER BY emp.unit DESC)
)
FROM emp
GROUP BY emp.row
returns two rows:
{"row" : 1, "units" : [{"Bob:"1B"},{"Frank":"1A"}]}
{"row" : 2, "units" : [{}, {"Steve":"2A"}]}
Last modified date: 10/30/2024