Purpose
The JSON_ARRAYAGG() function is an aggregate function that converts all column data into JSON data and combines all JSON data into a single JSON array structure.
Syntax
JSON_ARRAYAGG(expr [FORMAT JSON]
[ORDER SIBLINGS BY order_condition]
[ABSENT|NULL ON NULL,]
[RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size],]
[STRICT]);
Syntax
The syntax for the JSON_ARRAYAGG() function is as follows:
FORMAT JSON: specifies that the values generated by the parameter are JSON data, so that the output does not contain quotes (").NULL|ABSENT ON NULL: specifies that the clause is effective only when the result of the parameter expression isNULL.NULL ON NULLspecifies that the JSONNULLvalue is used as the current array element.ABSENT ON NULLspecifies that the array element is ignored.
STRICT: specifies that the output result is valid JSON data.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: specifies the return value type. TheVARCHAR2[size]can limit the length of the return value.ORDER SIBLINGS BY: anORDER BYclause that specifies the order of the JSON array members in the return value.
Examples
# Use the default parameters.
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;
OBE-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