Purpose
The JSON_TABLE() function provides a way to link relational tables with JSON-structured data. You can use the output of JSON_TABLE() in SQL queries as if it were a virtual relational table. JSON_TABLE() provides column outputs for each JSON value and multi-row (column) outputs for arrays within the JSON data. JSON_TABLE() must be used in the FROM clause of a 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_emtpy_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_emtpy_clause]]
[column_name FOR ORDINALITY]
[NESTED PATH path_literal COLUMNS (
# Nested structure, repeating the parent-level syntax
...
)]
)
)
Syntax
The syntax of the JSON_TABLE() function is described as follows:
json_value_return_type: Same as theRETURNINGclause ofJSON_VALUE().json_query_return_type: Same as theRETURNINGclause ofJSON_QUERY().FORMAT JSON: An optional clause. Use this clause when the data type ofexprisBLOB.path_literal: A general JSON path string.json_exists_on_emtpy_clause: Same as theemptyclause ofJSON_EXISTS().json_exists_on_error_clause: Same as theerrorclause ofJSON_EXISTS().json_query_on_error_clause: Same as theerrorclause ofJSON_QUERY().json_query_wrapper_clause: Same as theWRAPPERclause ofJSON_QUERY().- The
FOR ORDINALITYclause provides a row number for the current output row, which is anINTcolumn. The output value types are four, and except for theORDINALITYcolumn, the other three are similar in functionality and behavior toJSON_VALUE(),JSON_QUERY(), andJSON_EXISTS(). - The
[NESTED PATH ...]clause is a repeated nested structure. ERRORclause: Specifies the error handling for the initial inputexpr.
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 clause and the ORDINALITY clause.
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 the JSON_TABLE function and use the EMPTY 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 the JSON_TABLE function and use the EMPTY 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 the json_table function.
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 the json_table function.
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