Purpose
JSON_ARRAYAGG() is an aggregate function. It converts the data of all columns to JSON data and returns a single JSON array that contains those JSON values
Syntax
JSON_ARRAYAGG(expr [FORMAT JSON]
[ORDER SIBLINGS BY order_condition]
[ABSENT|NULL ON NULL,]
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[STRICT]);
Parameters
The parameters in the syntax are described as follows:
FORMAT JSON: specifies that the return value of the expression is JSON data and no\"will be added to the output result.NULL|ABSENT ON NULL: This clause takes effect only when the calculation result of the expression isNULL.NULL ON NULL: specifies that JSONNULLvalues will be used as elements of the current array.ABSENT ON NULL: specifies to ignore the array element.
STRICT: checks whether the output result is in JSON format.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: specifies the return value type.VARCHAR2[size]specifies the length of the return value.ORDER SIBLINGS BY: sorts the JSON array elements in the return value. This clause is anORDERR BYclause.
Examples
# Use default arguments.
obclient> CREATE TABLE id_table (id NUMBER);
obclient> INSERT INTO id_table VALUES(624);
obclient> INSERT INTO id_table VALUES(null);
obclient> INSERT INTO id_table VALUES(925);
obclient> INSERT INTO id_table VALUES(585);
obclient> SELECT JSON_ARRAYAGG(id) ID_NUMBERS FROM id_table;
+---------------+
| ID_NUMBERS |
+---------------+
| [624,925,585] |
+---------------+
1 row in set
# Use the FORMAT JSON clause.
obclient> CREATE TABLE json_table (json_doc VARCHAR2(100))
obclient> INSERT INTO json_table VALUES('[1]');
obclient> INSERT INTO json_table VALUES('[1,2]');
obclient> INSERT INTO json_table VALUES('[1,2,3]')
obclient> INSERT INTO json_table VALUES('{key:"value"}');
obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON) FROM json_table;
+-----------------------------------+
| JSON_ARRAYAGG(JSON_DOCFORMATJSON) |
+-----------------------------------+
| [[1],[1,2],[1,2,3],{key:"value"}] |
+-----------------------------------+
1 row in set
# Use the ABSENT ON NULL clause.
obclient> SELECT JSON_ARRAYAGG(id ORDER BY id ABSENT ON NULL) FROM id_table;
+----------------------------------------+
| JSON_ARRAYAGG(IDORDERBYIDABSENTONNULL) |
+----------------------------------------+
| [585,624,925] |
+----------------------------------------+
1 row in set
# Use the ORDER BY clause.
CREATE TABLE json_table (id NUMBER, text_data VARCHAR2(10), json_data JSON);
INSERT INTO json_table VALUES(624, 'test1', '[1]');
INSERT INTO json_table VALUES(null, 'test4', '[2]');
INSERT INTO json_table VALUES(925, 'test2', '[3]');
INSERT INTO json_table VALUES(585, 'test5', '[4]');
INSERT INTO json_table VALUES(585, 'test3', '[5]');
obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY text_data) FROM json_table;
+-------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYTEXT_DATA) |
+-------------------------------------------+
| ["test1","test2","test3","test4","test5"] |
+-------------------------------------------+
1 row in set
obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY id) FROM json_table;
+-------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYID) |
+-------------------------------------------+
| ["test5","test3","test1","test2","test4"] |
+-------------------------------------------+
1 row in set
obclient> SELECT JSON_ARRAYAGG(text_data ORDER BY id,text_data) FROM json_table;
+---------------------------------------------+
| JSON_ARRAYAGG(TEXT_DATAORDERBYID,TEXT_DATA) |
+---------------------------------------------+
| ["test3","test5","test1","test2","test4"] |
+---------------------------------------------+
1 row in set
# Use the STRICT clause.
CREATE TABLE json_table (json_doc VARCHAR2(100));
INSERT INTO json_table VALUES('[1]');
INSERT INTO json_table VALUES('[1,2]');
INSERT INTO json_table VALUES('[1,2,3]');
INSERT INTO json_table VALUES('{key:"value"}');
obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON STRICT) ID_NUMBERS FROM json_table;
ORA-00600: Invalid JSON text.
obclient> SELECT JSON_ARRAYAGG(json_doc FORMAT JSON) ID_NUMBERS FROM json_table;
+-----------------------------------+
| ID_NUMBERS |
+-----------------------------------+
| [[1],[1,2],[1,2,3],{key:"value"}] |
+-----------------------------------+
1 row in set