Purpose
JSON_TABLE() associates a relational table with JSON data. You can use the output result of this function in an SQL statement the same way you use a virtual relational table. This function provides column output for each JSON value, and provides multi-row (multi-column) output for a JSON array. This function must be used in the FROM clause of the SELECT statement.
Syntax
JSON_TABLE(
expr [FORMAT JSON]
[, json_path_literal]
[ERROR | NULL ON ERROR]
COLUMNS (
[column_name [json_value_return_type] EXISTS [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_empty_clause]]
[column_name [json_query_return_type] FORMAT JSON [ALLOW|DISALLOW SCALARS] [json_query_wrapper_clause] [PATH path_literal] [json_query_on_error_clause]]
[column_name [json_value_return_type] [TRUNCATE] [PATH path_literal] [json_exists_on_error_clause] [json_exists_on_empty_clause]]
[column_name FOR ORDINALITY]
[NESTED PATH path_literal COLUMNS (
# A nested structure, which inherits the parent-level syntax
...
)]
)
)
Parameters
The parameters in the syntax are described as follows:
json_value_return_type: This clause is equivalent to theRETURNINGclause of theJSON_VALUE()function.json_query_return_type: This clause is equivalent to theRETURNINGclause of theJSON_QUERY()function.FORMAT JSON: This clause is optional. It is required when the data type ofexprisBLOB.path_literal: specifies a JSON path in string format.json_exists_on_empty_clause: This clause is equivalent to theEMPTYclause of theJSON_EXISTS()function.json_exists_on_error_clause: This clause is equivalent to theERRORclause of theJSON_EXISTS()function.json_query_on_error_clause: This clause is equivalent to theERRORclause of theJSON_QUERY()function.json_query_wrapper_clause: This clause is equivalent to theWRAPPERclause of theJSON_QEURY()function.FOR ORDINALITY: specifies the line number of the current output line, which indicates a column ofINTtype. Four types of output values are available:ORDINALIY, as well as other three types of values similar to those of theJSON_VALUE(),JSON_QUERY(), andJSON_EXISTS()functions.[NESTED PATH ...]: specifies a repeated nested structure.ERROR: specifies how to handle input errors ofexpr.
Examples
# Use the NESTED COLUMN clause.
DROP TABLE jsonb_table_test;
CREATE TABLE jsonb_table_test (js VARCHAR2 (4000));
DELETE FROM jsonb_table_test;
INSERT INTO jsonb_table_test VALUES (
'[
{"c": [7], "d": [3], "a": 2, "b": [3,4]}
]'
);
obclient> SELECT jt.* FROM jsonb_table_test jtt,
json_table (jtt.js, '$[*]'
columns (
nested path '$.c[*]' COLUMNS (c int path '$'),
nested path '$.d[*]' COLUMNS (d int path '$'),
nested path '$.b[*]' COLUMNS (b int path '$'),
a int path '$.a'
)
) jt;
+------+------+------+------+
| C | D | B | A |
+------+------+------+------+
| NULL | NULL | 3 | 2 |
| NULL | NULL | 4 | 2 |
| NULL | 3 | NULL | 2 |
| 7 | NULL | NULL | 2 |
+------+------+------+------+
4 rows in set
# Use the NESTED COLUMN and ORDINALITY clauses.
obclient> INSERT INTO jsonb_table_test VALUES (
'[
{"a": 1, "b": [], "c": []},
{"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
{"a": 3, "b": [1, 2], "c": []},
{"a": 4, "b": [1, 2], "c": [123]}
]'
);
Query OK, 1 row affected
obclient> SELECT jt.* FROM jsonb_table_test jtt,
json_table (jtt.js, '$[*]'
columns (
nested path '$.c[*]' COLUMNS (c int path '$'),
nested path '$.b[*]' COLUMNS (b int path '$'),
n FOR ORDINALITY,
a int path '$.a'
)
) jt;
+------+------+------+------+
| C | B | N | A |
+------+------+------+------+
| NULL | NULL | 1 | 1 |
| NULL | 1 | 2 | 2 |
| NULL | 2 | 2 | 2 |
| NULL | 3 | 2 | 2 |
| 10 | NULL | 2 | 2 |
| NULL | NULL | 2 | 2 |
| 20 | NULL | 2 | 2 |
| NULL | 1 | 3 | 3 |
| NULL | 2 | 3 | 3 |
| NULL | 1 | 4 | 4 |
| NULL | 2 | 4 | 4 |
| 123 | NULL | 4 | 4 |
+------+------+------+------+
12 rows in set
# Query the json_value column of json_table and use the EMTPY clause.
obclient> SELECT jt.* FROM json_table (
'{"data": 123.45}',
'$'
columns (a CLOB path '$.nokey' default 'abc' on EMPTY)
) jt;
+------+
| A |
+------+
| abc |
+------+
1 row in set
# Query the json_value column of json_table and use the EMTPY clause.
obclient> SELECT jt.* FROM json_table (
'{"data": 123.45}',
'$'
columns (a CLOB path '$.nokey' NULL on EMPTY)
) jt;
+------+
| A |
+------+
| NULL |
+------+
1 row in set
obclient> SELECT jt.* FROM
json_table(
'{"details":
[ {"branch_code": "3020300", "products": [
"23D0980000000000",
"23E2980000000000",
"23E3980000000004"]},
{"branch_code": "3090100", "products": [
"23D0980000000000",
"23E2980000000000"]}],
"extras": ["AWUX1201293111", "AWUX1201293112"]
}',
'$.details[*]'
columns (
branch_code varchar2(100) path '$.branch_code',
nested path '$.products[*]' columns (product varchar2(100) path '$[*]')
)
) jt;
+-------------+------------------+
| BRANCH_CODE | PRODUCT |
+-------------+------------------+
| 3020300 | 23D0980000000000 |
| 3020300 | 23E2980000000000 |
| 3020300 | 23E3980000000004 |
| 3090100 | 23D0980000000000 |
| 3090100 | 23E2980000000000 |
+-------------+------------------+
5 rows in set
# Query the JSON_EXISTS column of json_table.
obclient> SELECT jt.* FROM json_table (
'{
"a": "1997-8-14 11:11:11",
"b" : 123,
"c": "varchar2"
}',
'$' columns (
a date EXISTS path '$.a',
b int EXISTS path '$.b' ERROR ON EMPTY,
c varchar2(32) format json path '$.c' NULL ON EMPTY
)
) jt;
+---------------------+------+----------+
| A | B | C |
+---------------------+------+----------+
| 1997-08-14 11:11:11 | 123 | varchar2 |
+---------------------+------+----------+
1 row in set
# Query the json_query column of json_table.
obclient> SELECT jt.* FROM json_table (
'{"data": [1, 2, 3, 3]}',
'$'
columns (a varchar2(1024) FORMAT JSON path '$.data[*]')
) jt;
+--------------+
| A |
+--------------+
| [1, 2, 3, 3] |
+--------------+
1 row in set