JSON_TABLE function

Description

The JSON_TABLE() function allows you to extract information from nested JSON data by specifying paths.

The JSON_TABLE() function converts JSON data from semi-structured data to structured data by extracting data from a JSON document and returning a relational table with specified columns. It creates a clear table structure by mapping different parts of the JSON data to rows and columns of a virtual table, which not only facilitates query operations but also allows users to insert this data into new tables or views for further processing.

The JSON_TABLE() function provides column outputs for each JSON value and multi-row (column) outputs for arrays within 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

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: Specifies the input expression to be converted to JSON data. If the expression cannot be converted to JSON data, an error will be returned.

• path_literal: Specifies the path as a string. If the input is not a valid path, an error will be returned.

• column_list: Specifies the column definitions. At least one column definition parameter must be included in the expression; otherwise, an error will be returned.

• column: Specifies the column definitions, which can be categorized into the following four types:

  • column_name FOR ORDINALITY: This clause provides a row number for the current output row. column_name is an INT type column.

  • column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]: This clause extracts the data specified by path_literal as JSON and converts it to the column type. If the value is missing, the optional json_value_on_empty_clause clause will be triggered.

  • column_name data_type EXISTS PATH path_literal: This clause returns 1 if any data exists at the location specified by path_literal, otherwise returns 0.

  • NESTED [PATH] path_literal COLUMNS (column_list): This clause indicates a nested structure and allows you to define the four types of columns mentioned above within it.

• data_type: Specifies the data type. Currently, all data types except ENUM and SET are supported in seekdb.

• alias: Specifies the table alias.

• json_value_on_empty_clause: Specifies the expected behavior when the data filtered by the path is null. The optional behavior includes three options: NULL, ERROR, and the default value DEFAULT.

  • NULL ON EMPTY: Sets the column to NULL, which is the default behavior.
  • DEFAULT json_string: Parses json_string as the default value to replace the JSON object or array. seekdb allows the default value to be any constant data of any type.
  • 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 optional behavior includes three options: NULL, ERROR, and the default value DEFAULT. The optional behavior is the same as json_value_on_empty_clause.

  The optional json_value_on_error_clause clause will be triggered in the following scenarios:

  • expr is 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 value data type is not large enough to accommodate the return value.

Examples

Parse user input data

The following example demonstrates how to convert 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 (0.003 sec)

Retrieve data from a table

The following example demonstrates 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 (0.004 sec)

Create a view

The following example demonstrates how to generate a view using the result 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 (0.001 sec)

The following example demonstrates how to view 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 (0.001 sec)

Insert data into a table

The following example demonstrates how to insert the result 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 (0.002 sec)

References

JSON data type