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 just like 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 as follows:
json_value_return_type: Same as theRETURNINGclause inJSON_VALUE().json_query_return_type: Same as theRETURNINGclause inJSON_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 theemptyclause inJSON_EXISTS().json_exists_on_error_clause: Same as theerrorclause inJSON_EXISTS().json_query_on_error_clause: Same as theerrorclause inJSON_QUERY().json_query_wrapper_clause: Same as theWRAPPERclause inJSON_QUERY().- The
FOR ORDINALITYclause provides a row number for the current output row, which is anINTcolumn. The output value types have four types, 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