JSON_agg_returning_clause
Use this clause to specify the data type of the character string returned by this function. You can specify the following data types:
VARCHAR2[(
size
[BYTE,CHAR])]
When specifying the
VARCHAR2
data type elsewhere in SQL, you are required to specify a size. However, in this clause you can omit the size.
If you omit this clause, or if you specify
VARCHAR2
but omit the
size
value, then
JSON_ARRAYAGG
returns a character string of type
VARCHAR2(4000)
.
Refer to
"
Data Types
"
for more information on the preceding data types.
STRICT
Specify the
STRICT
clause to verify that the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.
Refer to
JSON_OBJECT
for examples.
Examples
The following statements creates a table
id_table
, which contains ID numbers:
CREATE TABLE id_table (id NUMBER);
INSERT INTO id_table VALUES(624);
INSERT INTO id_table VALUES(null);
INSERT INTO id_table VALUES(925);
INSERT INTO id_table VALUES(585);
The following example constructs a JSON array from the ID numbers in table
id_table
:
SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS
FROM id_table;
ID_NUMBERS
-------------
[585,624,925]
