JSON Functions
JSON function#
The JSON function performs the function of creating a JSON document or retrieving necessary data from a JSON document. JSON functions can be classified into three types.
- JSON document creation function -JSON_ARRAY -JSON_OBJECT
- JSON document query function -JSON_EXISTS -JSON_QUERY -JSON_VALUE
- JSON document validation function -JSON_VALID
JSON_ARRAY#
Syntax#
JSON_ARRAY ::=#
JSON_ARRAY_element ::=#
JSON_on_null_clause ::=#
JSON_ARRAY_returning_clause ::=#
Description#
It receives list-type values as arguments, creates a JSON array, and returns it. JSON object, JSON array, SQL scalar, BOOLEAN, and NULL can be used as arguments to the JSON_ARRAY function. JSON_on_null_clause and JSON_ARRAY_returning_clause can be specified.
JSON_on_null_clause#
JSON_on_null_clause is used to specify the behavior when the argument contains NULL. If this clause is not specified, the default behavior is ABSENT ON NULL.
| syntax | Description |
|---|---|
NULL ON NULL |
If the function argument contains NULL, NULL is added to the array. |
ABSENT ON NULL (default action) |
If the function argument contains NULL, it is not added to the JSON array. |
JSON_returning_clause#
You can specify the data type of the return value of the JSON_ARRAY function. Only CHAR, VARCHAR, JSON, and CLOB can be specified. If precision is not explicitly specified for CHAR or VARCHAR types, the default value of 1 is set. However, specifying an unsupported data type will result in an error.
If this clause is not specified, the data type of the return value is set to VARCHAR, and precision is automatically calculated according to the size of the input data.
Example#
iSQL> SELECT JSON_ARRAY(1,'altibase',NULL) JARRAY FROM DUAL;
JARRAY
-----------------------
[1,"altibase"]
1 row selected.
iSQL> SELECT JSON_ARRAY(1,'altibase',JSON_ARRAY(1,2,3),NULL NULL ON NULL) JARRAY FROM DUAL;
JARRAY
-----------------------
[1,"altibase",[1,2,3],null]
1 row selected.
iSQL> SELECT JSON_ARRAY(1,'altibase',JSON_ARRAY(1,2,3),NULL NULL ON NULL RETURNING JSON) JARRAY FROM DUAL;
JARRAY
------------------------------------------------------
[1,"altibase",[1,2,3],null]
1 row selected.
JSON_EXISTS#
Syntax#
JSON_EXISTS ::=#
JSON_EXISTS_on_error_clause ::=#
JSON_EXISTS_on_empty_clause ::=#
Description#
Returns whether a value corresponding to a JSON path expression exists in JSON data. JSON_EXISTS_on_error_clause and JSON_exists_on_empty_clause can be specified.
JSON_EXISTS_on_error_clause#
Specifies the behavior when an error occurs during execution of the JSON_EXISTS function. If this clause is not specified, the default behavior is FALSE ON ERROR.
| Syntax | Description |
|---|---|
ERROR ON ERROR |
If an error occurs, the error is returned. |
FALSE ON ERROR (default action) |
If an error occurs, returns FALSE. |
TRUE ON ERROR |
If an error occurs, returns TRUE. |
JSON_EXISTS_on_empty_clause#
Specifies the behavior when there is no result while executing the JSON_EXISTS function. If this clause is not specified, the default behavior is FALSE ON EMPTY.
| Syntax | Description |
|---|---|
ERROR ON EMPTY |
If there are no search results, an error is returned. |
FALSE ON EMPTY |
If there are no search results, FALSE is returned. |
TRUE ON EMPTY |
If there are no search results, returns TRUE. |
Example#
iSQL> SELECT 1 AS result FROM DUAL WHERE JSON_EXISTS('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NATION?(@=="KOREA")');
RESULT
--------------
1
1 row selected.
iSQL> SELECT 1 AS result FROM DUAL WHERE JSON_EXISTS('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NATION?(@=="USA")');
RESULT
--------------
No rows selected.
iSQL> SELECT 1 AS result FROM DUAL WHERE JSON_EXISTS('invalid_json', '$' TRUE ON ERROR);
RESULT
--------------
1
1 row selected.
iSQL> SELECT 1 AS result FROM DUAL WHERE JSON_EXISTS('invalid_json', '$' FALSE ON ERROR);
RESULT
--------------
No rows selected.
iSQL> SELECT 1 AS result FROM DUAL WHERE JSON_EXISTS('invalid_json', '$' ERROR ON ERROR);
[ERR-314C6 : Invalid JSON data.
line 1:
invalid_json
^
]
JSON_OBJECT#
Syntax#
JSON_OBJECT ::=#
JSON_on_null_clause ::=#
JSON_OBJECT_returning_clause ::=#
Description#
It receives a key and value pair as arguments and creates and returns a JSON object. Only character values can be used as keys, and NULL cannot be used as a key. The value pairs with the key and can be of character type, numeric type, JSON object, JSON array, BOOLEAN, or NULL. JSON_on_null_clause and JSON_OBJECT_returning_clause can be specified.
JSON_on_null_clause#
You can specify whether to include data with a NULL value in the JSON object. If this clause is not specified, it operates as NULL ON NULL.
| syntax | Description |
|---|---|
NULL ON NULL (default behavior) |
Data with a NULL value is included in the JSON object. |
ABSENT ON NULL |
Data with a NULL value is not included in the JSON object. |
JSON_OBJECT_returning_clause#
You can specify the data type of value to be returned as a result of the JSON_OBJECT function. Only CHAR, VARCHAR, JSON, and CLOB can be specified. If precision is not explicitly specified for CHAR and VARCHAR types, the default value of 1 is set. However, specifying an unsupported data type will result in an error.
If this clause is not specified, the data type of the return value is set to VARCHAR, and precision is automatically calculated according to the size of the input data.
Example#
iSQL> SELECT JSON_OBJECT('ID', 'AA000001', 'NAME', 'HONG GILDONG', 'NATION', 'KOREA') j_obj FROM DUAL;
J_OBJ
------------------------------------------------------------------------------------------------
{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}
1 row selected.
iSQL> SELECT JSON_OBJECT('USER1', '{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', 'PURCHASE_NO', '[123,345,678]') j_obj FROM DUAL;
J_OBJ
---------------------------------------------------------------------------------------------------------------
{"USER1":"{\"ID\":\"AA000001\",\"NAME\":\"HONG GILDONG\",\"NATION\":\"KOREA\"}","PURCHASE_NO":"[123,345,678]"}
1 row selected.
iSQL> SELECT JSON_OBJECT('ID', 'AA000001', 'NAME', 'HONG GILDONG', 'NATION', NULL) j_obj FROM DUAL;
J_OBJ
-----------------------------------------------------------------------------------------
{"ID":"AA000001","NAME":"HONG GILDONG","NATION":null}
1 row selected.
iSQL> SELECT JSON_OBJECT('ID', 'AA000001', 'NAME', 'HONG GILDONG', 'NATION', NULL ABSENT ON NULL) j_obj FROM DUAL;
J_OBJ
-----------------------------------------------------------------------------------------
{"ID":"AA000001","NAME":"HONG GILDONG"}
1 row selected.
iSQL> SELECT JSON_OBJECT('ID', 'AA000001', 'NAME', 'HONG GILDONG', 'NATION', 'KOREA' RETURNING JSON) j_obj FROM DUAL;
J_OBJ
--------------------------------------------------------------------------------------------------------
{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}
1 row selected.
iSQL> SELECT JSON_OBJECT('ID', 'AA000001', 'NAME', 'HONG GILDONG', 'NATION', 'KOREA' RETURNING CLOB) j_ojb FROM DUAL;
J_OBJ
-----------------------------------------------------------------------------------
{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}
1 row selected.
JSON_QUERY#
Syntax#
JSON_QUERY ::=#
JSON_QUERY_returning_clause ::=#
JSON_QUERY_wrapper_clause ::=#
JSON_QUERY_on_error_clause ::=#
JSON_QUERY_on_empty_clause ::=#
Description#
Finds and returns the value corresponding to the JSON path expression in the JSON document. JSON_QUERY_returning_clause, JSON_QUERY_wrapper_clause, JSON_QUERY_on_error_clause, JSON_QUERY_on_empty_clause can be specified.
JSON_QUERY_returning_clause#
Specifies the data type of value to be returned as a result of the JSON_QUERY function. Only CHAR, VARCHAR, JSON, and CLOB can be specified. At this time, if precision is not specified in CHAR and VARCHAR types, the default value is set to 1. On the other hand, if you specify an unsupported data type, an error will occur.
If this clause is not specified, the data type of the return value is set to VARCHAR, and precision is automatically calculated according to the size of the input data.
JSON_QUERY_wrapper_clause#
You can specify whether to return the results of the JSON_QUERY function in the form of a JSON array. If this clause is not specified, it operates WITHOUT WRAPPER. UNCONDITIONAL and ARRAY can be omitted.
| Syntax | Description |
|---|---|
WITH (ARRAY) WRAPPER |
The results of the JSON_QUERY function are returned in the form of a JSON array. If there are two or more results from the JSON_QUERY function, the WITH WRAPPER clause must be specified and returned in the form of a JSON array. |
WITHOUT (ARRAY) WRAPPER |
The results of the JSON_QUERY function are not returned in array form. |
WITH UNCONDITIONAL (ARRAY) WRAPPER |
Equivalent to specifying the WITH WRAPPER clause. |
WITH CONDITIONAL (ARRAY) WRAPPER |
It is returned in array form only when there are two or more results of the JSON_QUERY function. |
JSON_QUERY_on_error_clause#
You can specify the action when an error occurs during function execution. If this clause is not specified, the default operation is NULL ON ERROR.
| Syntax | Description |
|---|---|
NULL ON ERROR (default behavior) |
If an error occurs, NULL is returned. |
ERROR ON ERROR |
If an error occurs, the error is returned. |
JSON_QUERY_on_empty_clause#
You can specify the action when no results are returned during execution of the JSON_QUERY function. If this clause is not specified, it operates as NULL ON EMPTY.
| Syntax | Description |
|---|---|
NULL ON EMPTY (default behavior) |
If there are no search results, NULL is returned. |
ERROR ON EMPTY |
If there are no search results, an error is returned. |
Example#
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NAME') AS name FROM DUAL;
NAME
-----------------------
"HONG GILDONG"
1 row selected.
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NATION?(@=="KOREA")') AS nation FROM DUAL;
NATION
-----------------------
"KOREA"
1 row selected.
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","ORDER_ID":[3123,2412,5286]}', '$.ORDER_ID') AS name FROM DUAL;
NAME
--------------------------------------------------------------------------------------------------------
[3123,2412,5286]
1 row selected.
-- WITH (UNCONDITONAL) (ARRAY) WRAPPER
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","ORDER_ID":[3123,2412,5286]}', '$.ORDER_ID' WITH WRAPPER) AS name FROM DUAL;
NAME
--------------------------------------------------------------------------------------------------------
[[3123,2412,5286]]
1 row selected.
-- WITH CONDITONAL (ARRAY) WRAPPER
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","ORDER_ID":[3123,2412,5286]}', '$.ORDER_ID' WITH CONDITIONAL WRAPPER) AS name FROM DUAL;
NAME
--------------------------------------------------------------------------------------------------------
[3123,2412,5286]
1 row selected.
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","ORDER_ID":[3123,2412,5286]}', '$.*' ERROR ON ERROR) AS name FROM DUAL;
[ERR-314C0 : An array wrapper is required.]
iSQL> SELECT JSON_QUERY('{"ID":"AA000001","NAME":"HONG GILDONG","ORDER_ID":[3123,2412,5286]}', '$.*' WITH WRAPPER ERROR ON ERROR) AS name FROM DUAL;
NAME
--------------------------------------------------------------------------------------------------------
["AA000001","HONG GILDONG",[3123,2412,5286]]
1 row selected.
iSQL> SELECT JSON_QUERY('invalid_json', '$' NULL ON ERROR) AS jdata FROM DUAL;
JDATA
--------------------------------------------------------------------------------------------------------
1 row selected.
iSQL> SELECT JSON_QUERY('invalid_json', '$' ERROR ON ERROR) AS jdata FROM DUAL;
[ERR-314C6 : Invalid JSON data.
line 1:
invalid_json
^
]
JSON_VALID#
Syntax#
JSON_VALID ::=#
Description#
It checks whether the entered JSON document is in the correct JSON format and returns the result. Returns 1 if the JSON document is in JSON format, otherwise 0.
Example#
iSQL> SELECT JSON_VALID('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}') AS valid FROM DUAL;
VALID
--------------
1
1 row selected.
iSQL> SELECT JSON_VALID('{"INVALID OBJ"}') AS valid FROM DUAL;
VALID
--------------
0
1 row selected.
JSON_VALUE#
Syntax#
JSON_VALUE ::=#
JSON_VALUE_returning_clause ::=#
JSON_VALUE_on_error_clause ::=#
JSON_VALUE_on_empty ::=#
Description#
Finds the value corresponding to the JSON path expression in the JSON document and returns it as a scalar value. JSON_VALUE_returning_clause, JSON_VALUE_on_error_clause, JSON_VALUE_on_empty_clause can be specified.
JSON_VALUE_returning_clause#
JSON_VALUE Specifies the type of value to be returned as a result of the function. Only CHAR, VARCHAR, CLOB, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, NUMBER, and NUMERIC can be specified. At this time, if precision is not specified in CHAR and VARCHAR types, the default value is set to 1. On the other hand, if you specify an unsupported data type, an error will occur.
If this clause is not specified, the data type of the return value is set to VARCHAR, and precision is automatically calculated according to the size of the input data.
JSON_VALUE_on_error_clause#
You can specify the action when an error occurs while executing the JSON_VALUE function. If this clause is not specified, the default operation is NULL ON ERROR.
| Syntax | Description |
|---|---|
NULL ON ERROR (default behavior) |
If an error occurs, NULL is returned. |
ERROR ON ERROR |
If an error occurs, an error is returned. |
DEFAULT expr ON ERROR |
If an error occurs, expr is returned |
JSON_value_on_empty_clause#
You can specify the action when no results are returned during JSON_VALUE function execution. If this clause is not specified, it operates as NULL ON EMPTY.
| syntax | Description |
|---|---|
NULL ON EMPTY (default behavior) |
If there are no search results, NULL is returned. |
ERROR ON EMPTY |
If there are no search results, an error is returned. |
DEFAULT expr ON ERROR |
If there are no search results, expr is returned. |
Example#
iSQL> SELECT JSON_VALUE('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NAME') AS name FROM DUAL;
NAME
-----------------------
HONG GILDONG
1 row selected.
iSQL> SELECT JSON_VALUE('{"ID":"AA000001","NAME":"HONG GILDONG","NATION":"KOREA"}', '$.NATION?(@=="KOREA")') AS nation FROM DUAL;
NATION
--------------
KOREA
1 row selected.
iSQL> SELECT JSON_VALUE('invalid_json', '$' NULL ON ERROR) AS jdata FROM DUAL;
JDATA
--------------------------------------------------------------------------------------------------------
1 row selected.
iSQL> SELECT JSON_VALUE('invalid_json', '$' ERROR ON ERROR) AS jdata FROM DUAL;
[ERR-314C6 : Invalid JSON data.
line 1:
invalid_json
^
]
iSQL> SELECT JSON_VALUE('invalid_json', '$' DEFAULT 'ERROR' ON ERROR) AS jdata FROM DUAL;
JDATA
--------------------------------------------------------------------------------------------------------
ERROR
1 row selected.
iSQL> SELECT JSON_VALUE('invalid_json', '$' RETURNING VARCHAR(3) DEFAULT 'ERROR' ON ERROR) AS jdata FROM DUAL;
[ERR-314C1 : The default value exceeds the maximum length.]
iSQL> SELECT JSON_VALUE('invalid_json', '$' RETURNING VARCHAR(10) DEFAULT 'ERROR' ON ERROR) AS jdata FROM DUAL;
JDATA
--------------------------------------------------------------------------------------------------------
ERROR
1 row selected.