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 like a virtual relational table. JSON_TABLE() provides column output for each JSON value and multi-row (column) output for arrays within JSON. 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: Optional. This clause is required when the data type ofexprisBLOB.path_literal: A general JSON path string.json_exists_on_emtpy_clause: Same as theemtpyclause 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 have four types. Except for theORDINALITYcolumn, the other three are similar to the functionality and behavior ofJSON_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 column.
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 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 json_table 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