Purpose
The JSON_ARRAY() function calculates a series of SQL scalar expressions, converts each expression to a JSON value, and constructs a JSON array containing these JSON values.
The JSON_ARRAY() function can specify the result of the parameter expressions as a JSON object, JSON array, numeric literal, text literal, date, timestamp, or NULL. This function converts numeric literals to JSON numeric values and text literals to JSON string values. Date and timestamp data types are printed as JSON strings in the generated JSON object or array, following the ISO 8601 date format.
Syntax
JSON_ARRAY (
[val [FORMAT JSON], val [FORMAT JSON] ...]
[ABSENT|NULL ON NULL]
[RETURNING JSON | CLOB| BLOB| VARCAHR2| VARCHAR2[size]]
[STRICT] )
Syntax
The syntax of the JSON_ARRAY() function is as follows:
FORMAT JSON: an optional clause that specifies the input string as JSON text, and no quotes ('"') are added when outputting.ABSENT|NULL ON NULL: an optional clause that specifies how to handle null values.NULL ON NULL: specifies that the current expression returns a JSONNULLvalue.ABSENT ON NULL: specifies that the current expression is ignored if the expression evaluates toNULL.RETURNING JSON | CLOB| BLOB| VARCAHR2| VARCHAR2[size]: an optional clause that specifies the return value type.STRICT: an optional clause that validates the output is in a valid JSON format.
Examples
# Use nested expressions and the NULL ON NULL clause
obclient> SELECT JSON_ARRAY (JSON_ARRAY(1,2,3),100,'California',null NULL ON NULL) "JSON Array Example"
FROM DUAL;
+-----------------------------------+
| JSON Array Example |
+-----------------------------------+
| ["[1,2,3]",100,"California",null] |
+-----------------------------------+
1 row in set
# The ABSENT ON NULL clause is effective, and the result does not contain Null
obclient> SELECT JSON_ARRAY( JSON_ARRAY(1,2,3),100,'California', null ABSENT ON NULL) "JSON Array Example"
FROM DUAL;
+----------------------------------+
| JSON Array Example |
+----------------------------------+
| ["[1, 2, 3]", 100, "California"] |
+----------------------------------+
1 row in set
# The FORMAT JSON clause is effective, and the current expression input string is treated as a JSON type. The output result is not enclosed in '\"'.
obclient> SELECT JSON_ARRAY('{"a":"a-value", "b" : "b-value"}' format json RETURNING CLOB)
FROM DUAL;
+--------------------------------------------------------------------+
| JSON_ARRAY('{"A":"A-VALUE","B":"B-VALUE"}'FORMATJSONRETURNINGCLOB) |
+--------------------------------------------------------------------+
| [{"a": "a-value", "b": "b-value"}] |
+--------------------------------------------------------------------+
1 row in set
# The input does not contain the FORMAT JSON clause, and the output string is enclosed in '\"'
obclient> SELECT JSON_ARRAY('{"a":"a-value", "b" : "b-value"}' RETURNING CLOB) FROM DUAL;
+----------------------------------------------------------+
| JSON_ARRAY('{"A":"A-VALUE","B":"B-VALUE"}'RETURNINGCLOB) |
+----------------------------------------------------------+
| ["{\"a\":\"a-value\", \"b\" : \"b-value\"}"] |
+----------------------------------------------------------+
1 row in set
# Specify the return value type as VARCHAR2
obclient> SELECT JSON_ARRAY('{"a":"a-value", "b" : "b-value"}' format json RETURNING VARCHAR2)
FROM DUAL;
+------------------------------------------------------------------------+
| JSON_ARRAY('{"A":"A-VALUE","B":"B-VALUE"}'FORMATJSONRETURNINGVARCHAR2) |
+------------------------------------------------------------------------+
| [{"a": "a-value", "b": "b-value"}] |
+------------------------------------------------------------------------+
1 row in set