Purpose
JSON_TABLE() can extract required information from nested JavaScript Object Notation (JSON) data based on the specified path.
It converts the extracted semi-structured JSON data into structured data. To be specific, the function extracts data from a JSON document and returns it as a relational table with specified columns. It creates a clear table schema by mapping different parts of the JSON data to the rows and columns of a virtual table. This facilitates query operations and allows you to insert the data into a new table or view for further processing.
JSON_TABLE() provides column output for each JSON value, and provides multi-row (multi-column) output for a JSON array.
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
Considerations
JSON_TABLE() must be used in the FROM clause of a SELECT statement.
The arguments in the syntax of the JSON_TABLE() function are described as follows:
expr: the expression that evaluates to JSON data. If the expression does not evaluate to JSON data, an error is reported.path_literal: the string that specifies the JSON path. If you set the parameter to a value of other types or an invalid path, an error is reported.column_list: the list of column definitions. The expression must include at least one column definition; otherwise, an error is reported.column: the specific column definition. The following column definition syntaxes are supported:column_name FOR ORDINALITYFOR: provides an ID for a row.column_nameis a column of theINTtype.column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]: extracts JSON data frompath_literaland forcibly converts the data to the column type. A missing value triggers the optional clausejson_value_on_empty_clause.column_name data_type EXISTS PATH path_literal: returns1if any data exists in the path specified bypath_literal, and0if otherwise.NESTED [PATH] path_literal COLUMNS (column_list): indicates a nested structure wherecolumnof any of the other three column definition syntaxes can be further defined.
data_type: the data type. OceanBase Database in MySQL-compatible mode supports all data types exceptENUMandSET.alias: the table alias.json_value_on_empty_clause: the expected behavior performed when the data filtered by the path is empty. Valid values:NULL ON EMPTY: sets the column toNULL, which is the default behavior.DEFAULT json_string: parsesjson_stringas the default value to replace the missing JSON object or array. OceanBase Database allows you to use a constant of any type as the default value.ERROR ON EMPTY: throws an error.
json_value_on_error_clause: the expected behavior performed to override an error that occurs during expression evaluation. The valid values are the same as those ofjson_value_on_empty_clause.The optional clause
json_value_on_error_clauseis triggered in the following scenarios:exprdoes not evaluate to properly formatted JSON data.The JSON path expression points to a non-scalar value.
The JSON path expression points to no data.
The length of the data type specified for the return value is not large enough to hold the return value.
Examples
Parse user input data
The following example parses the JSON data entered by a user and returns 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
Extract data from a table
The following example extracts JSON data from a table and returns a relational table.
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 generates a view based on the values returned by 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
Check the 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 values returned by 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 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