Purpose
The JSON_TABLE() function's path subexpression allows you to extract desired information from nested JSON data.
The JSON_TABLE() function is used to convert semi-structured JSON data into structured data. It extracts data from a JSON document and returns a relational table with specified columns. By mapping different parts of the JSON data to rows and columns of a virtual table, it creates a clear table structure. This not only facilitates query operations but also allows users to insert this data into new tables or views for further processing.
JSON_TABLE() provides column output for each JSON value, and also multi-row (column) output for arrays within the JSON.
Syntax
JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias
column_list:
column[, column...]
column:
column_name FOR ORDINALITY
| column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]
| column_name data_type EXISTS PATH path_literal
| NESTED [PATH] path_literal COLUMNS (column_list)
json_value_on_empty_clause:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
json_value_on_error_clause:
{NULL | DEFAULT json_string | ERROR} ON ERROR
Description
The JSON_TABLE() function must be used in the FROM clause of a SELECT statement.
The syntax of the JSON_TABLE() function is as follows:
expr: the input expression to be converted into JSON data. If the expression cannot be converted into JSON data, an error is reported.path_literal: A string specifying the path. Entering data of other types or an invalid path will result in an error.column_list: Specifies the column definition information to be input. The expression must contain at least one column definition parameter; otherwise, an error will be reported.column: specifies the column definition. The column definition can be one of the following four types:The
column_name FOR ORDINALITY FORclause is used to provide a row number for the current output row, wherecolumn_nameis anINTtype column.The
column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]clause is used to extract data specified bypath_literalas JSON and force it to the column type. If a missing value is encountered, the optionaljson_value_on_empty_clauseclause is triggered.column_name data_type EXISTS PATH path_literalclause: If any data exists at the location specified bypath_literal, this column returns 1; otherwise, it returns 0.The
NESTED [PATH] path_literal COLUMNS (column_list)clause indicates a repeated nested structure, within which you can continue to define the four column types ofcolumn.
data_type: the data type. Currently, all data types supported by OceanBase AI Database except forENUMandSETare supported.alias: the alias of the table.json_value_on_empty_clause: Specifies the user's desired behavior when data filtered by the PATH clause is null. Valid values areNULL,ERROR, and the default valueDEFAULT.NULL ON EMPTY: Sets the column toNULL, which is the default behavior.DEFAULT json_string: Parsejson_stringinto a default value to replace the JSON object or array. OceanBase AI Database allows the default value of a behavior to be a constant data of any type.ERROR ON EMPTY: Throws an error.
json_value_on_error_clause: If an error occurs during the evaluation of an expression, you can use one of three expected behaviors to override the error:NULL,ERROR, or the default valueDEFAULT. The available behaviors are the same as those specified in thejson_value_on_empty_clause.The optional
json_value_on_error_clauseclause is triggered in the following scenarios.expris not valid JSON data.Non-scalar values detected when calculating JSON data using JSON path expressions
No match was found when calculating the JSON data using a JSON path expression.
The specified return value data type is not large enough to accommodate the return value.
Examples
Parse user input data
The following example converts user-input JSON data into a relational table.
SELECT * FROM
JSON_TABLE(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a'
DEFAULT '33' ON EMPTY
DEFAULT '66' ON ERROR,
jsn_path JSON PATH '$.a' DEFAULT '{"x":33}' ON EMPTY,
jexst INT EXISTS PATH '$.b')
) AS tt;
The query result is as follows:
+------+-------+-----------+-------+
| id | jpath | jsn_path | jexst |
+------+-------+-----------+-------+
| 1 | 33 | {"x": 33} | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 33 | {"x": 33} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 66 | [1, 2] | 0 |
+------+-------+-----------+-------+
5 rows in set
Retrieve data from a table
The following example shows how to retrieve JSON data from a table and expand it into structured data.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT, jd JSON);
INSERT INTO t1 VALUES (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
The query result is as follows:
+------+------+------+
| id | jid | val |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 5 |
| 2 | 1 | 2 |
| 2 | 2 | 4 |
| 2 | 3 | 6 |
+------+------+------+
6 rows in set
Create a view
The following example shows how to generate a view using the return value of the JSON_TABLE() function.
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
The query result is as follows:
+------+
| a |
+------+
| 2 |
+------+
1 row in set
The following example shows how to view a view definition.
SHOW CREATE VIEW v1;
DROP VIEW v1;
The query result is as follows:
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE VIEW `v1` AS select `jt`.`a` AS `a` from JSON_TABLE('[{\"a\": 1, \"b\": 2}]' , '$[*]' columns (a INTEGER path '$.b' )) jt | utf8mb4 | utf8mb4_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set
Insert data into a table
The following example inserts the return value of the JSON_TABLE() function into a table.
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(id INT, a TINYINT, b VARCHAR(5), c DATE, d DECIMAL);
INSERT INTO t2 SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(5) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t2 ORDER BY id;
DROP TABLE t2;
The query result of the example is as follows:
+------+------+------+------------+-------+
| id | a | b | c | d |
+------+------+------+------------+-------+
| 1 | 1 | abc | NULL | NULL |
| 2 | 2 | abcd | NULL | NULL |
| 3 | 111 | xyz | NULL | NULL |
| 4 | NULL | NULL | 2001-01-01 | 43200 |
+------+------+------+------------+-------+
4 rows in set