Purpose
The JSON_ARRAY() function evaluates 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 expression 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 is JSON text and no quotes ('"') are needed when outputting.ABSENT|NULL ON NULL: An optional clause that specifies how to handle null values.NULL ON NULL: The current expression returns aNULLvalue in JSON.ABSENT ON NULL: If the expression evaluates toNULL, the current expression is ignored.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, treating the current expression's input string as JSON type, and the output result does not have '\"' added
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
# If the input does not have FORMAT JSON, the output string is added with '\"'
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