Purpose
JSON_ARRAY() calculates a series of SQL scalar expressions, converts each expression to a JSON value, and constructs a JSON array of the JSON values.
JSON_ARRAY() allows you to set the type of the calculation result of an expression to a JSON object, a JSON array, a numeric literal, a text literal, a date, a timestamp, or NULL. This function converts numeric literals to JSON numeric values and text literals to JSON string values. Date or timestamp data contained in the generated JSON object or array is displayed as a JSON string in 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] )
Parameters
The parameters in the syntax are described as follows:
FORMAT JSON: specifies that the input string is a JSON text and no\"will be added the output result. This clause is optional.ABSENT|NULL ON NULL: specifies how to handle NULL values. This clause is optional.NULL ON NULL: specifies that the return value of the current expression isNULL.ABSENT ON NULL: specifies to ignore the current expression if its calculation result isNULL.RETURNING JSON | CLOB| BLOB| VARCAHR2| VARCHAR2[size]: specifies the return value type. This clause is optional.STRICT: checks whether the output result is in JSON format. This clause is optional.
Examples
# Use a nested expression 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 takes effect, and the result does not contain NULL values.
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 takes effect. The input string of the current expression is treated as a JSON string. No \" is added to the output result.
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 string does not contain the FORMAT JSON clause. \" is added to the output result.
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
# The return value type is specified 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