JSON_ARRAYAGG
Converts aggregation of each input SQL expression to a JSON value, and returns a single JSON array that contains those JSON values.
Syntax
<JSON array aggregate constructor> ::= “JSON_ARRAYAGG” “(“ <JSON value expression>
[ "ORDER BY" <sort specification list> ] [ "NULL ON NULL" | "ABSENT ON NULL" ] “)”
<sort specification list> ::= <sort specification> [ { "," <sort specification> }... ]
<sort specification> ::= <sort key> [ "ASC" | "DESC" ] [ NULLS FIRST | NULLS LAST ]
Rules
1. Return type is String;
2. ABSENT ON NULL is implicit;
3. Besides numeric, boolean, null types, JSON values should be string or cast as string.
4. Return null if rows obtained by the function is 0.
Examples
SELECT JSON_ARRAYAGG(customerid) a1 FROM /shared/examples/ds_orders/tutorial/customers GROUP BY customerid HAVING JSON_ARRAYAGG(customerid)='[]'