Purpose
The JSON_ARRAYAGG() function is an aggregate function that converts all column data into JSON data and combines all the 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 of the JSON_ARRAYAGG() function is described as follows:
FORMAT JSON: Declares that the value generated by this parameter is JSON data, so there will be no quotes('"') in the output.NULL|ABSENT ON NULL: Indicates that this clause is effective only when the result of the parameter expression isNULL.NULL ON NULLuses the JSONNULLvalue as the current array element.ABSENT ON NULLignores the array element.
STRICT: Used to verify that the output result is valid JSON data.RETURNING CLOB|BLOB|JSON|VARCHAR2|VARCHAR2[size]: Used to specify the return value type, whereVARCHAR2[size]can limit the length of the return value.ORDER SIBLINGS BY:ORDER BYclause, which is used to sort the JSON array elements 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