Purpose
The JSON_TABLE() function extracts information from nested JSON data by using a subpath.
The JSON_TABLE() function converts JSON data from semi-structured data to 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 that facilitates query operations and allows you to insert the data into a new table or view for further processing.
The JSON_TABLE() function provides column outputs for each JSON value and multi-row (column) outputs for arrays in the JSON data.
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
Purpose
The JSON_TABLE() function must be used in the FROM clause of a SELECT statement.
The syntax of the JSON_TABLE() function is described as follows:
expr: specifies the input expression to be converted to JSON data. If the expression cannot be converted to JSON data, an error is returned.path_literal: specifies the path as a string. If the input data is not of the correct path type, an error is returned.column_list: specifies the column definitions. At least one column definition must be provided in the expression, otherwise an error is returned.column: specifies the column definitions. The column definitions can be divided into the following four types:column_name FOR ORDINALITY: specifies the row number for the current output row.column_nameis anINTcolumn.column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]: extracts the data specified bypath_literalas JSON data and forcibly converts it to the column type. If the value is missing, the optionaljson_value_on_empty_clauseclause is triggered.column_name data_type EXISTS PATH path_literal: returns 1 if any data exists at the location specified bypath_literal, otherwise returns 0.NESTED [PATH] path_literal COLUMNS (column_list): specifies a nested structure. You can continue to define the four column types in thecolumnclause.
data_type: specifies the data type. Currently, all data types exceptENUMandSETare supported in OceanBase Database in MySQL mode.alias: specifies the table alias.json_value_on_empty_clause: specifies the expected behavior when the data filtered by the path is a null value. The expected behavior can be one of the following three options:NULL,ERROR, or the default valueDEFAULT.NULL ON EMPTY: sets the column toNULL, which is the default behavior.DEFAULT json_string: parsesjson_stringas the default value to replace the JSON object or array. OceanBase Database allows the default value to be any type of constant data.ERROR ON EMPTY: throws an error.
json_value_on_error_clause: specifies the expected behavior when an error occurs during the execution of the expression. The expected behavior can be one of the following three options:NULL,ERROR, or the default valueDEFAULT. The expected behavior is the same as that specified by thejson_value_on_empty_clauseclause.The following scenarios will trigger the optional
json_value_on_error_clauseclause:expris not valid JSON data.A non-scalar value is found when calculating JSON data using a JSON path expression.
No matching item is found when calculating JSON data using a JSON path expression.
The specified return 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 retrieves JSON data from a table and expands 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 generates a view based on 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 views 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 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 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