Purpose
JSON_TABLE() converts semi-structured JSON data into structured data. That is, the function extracts data from a JSON document and returns it as a relational table with specified columns.
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
Description
JSON_TABLE() must be used in the FROM clause of a SELECT statement.
The parameters in the syntax 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 types of columns 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 values frompath_literalas JSON data and then force converts it 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, and0otherwise.NESTED [PATH] path_literal COLUMNS (column_list): indicates a nested structure wherecolumnof any of the four types can be further defined.
data_type: the data type. The MySQL mode of OceanBase Database 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, which may beNULL,ERROR, orDEFAULT.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, which may beNULL,ERROR, orDEFAULT. The options are the same as those ofjson_value_on_empty_clause.The following scenarios trigger the optional clause
json_value_on_error_clause: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 value.
Examples
Parse user input data
The following example parses the JSON data entered by a user and returns it as 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 it as 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
