Movatterモバイル変換

This section contains information about JSON functions that convert JSON data to tabular data. MySQL 8.4 supports one such function,JSON_TABLE().

JSON_TABLE(expr,path COLUMNS (column_list) [AS]alias)

Extracts data from a JSON document and returns it as a relational table having the specified columns. The complete syntax for this function is shown here:

JSON_TABLE(expr,path COLUMNS (column_list))   [AS]aliascolumn_list:column[,column][, ...]column:name FOR ORDINALITY    |nametype PATHstring path [on_empty] [on_error]    |nametype EXISTS PATHstring path    |  NESTED [PATH]path COLUMNS (column_list)on_empty:    {NULL | DEFAULTjson_string | ERROR} ON EMPTYon_error:    {NULL | DEFAULTjson_string | ERROR} ON ERROR

expr: This is an expression that returns JSON data. This can be a constant ('{"a":1}'), a column (t1.json_data, given tablet1 specified prior toJSON_TABLE() in theFROM clause), or a function call (JSON_EXTRACT(t1.json_data,'$.post.comments')).

path: A JSON path expression, which is applied to the data source. We refer to the JSON value matching the path as therow source; this is used to generate a row of relational data. TheCOLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data.

Thealias is required. The usual rules for table aliases apply (seeSection 11.2, “Schema Object Names”).

This function compares column names in case-insensitive fashion.

JSON_TABLE() supports four types of columns, described in the following list:

on empty, if specified, determines whatJSON_TABLE() does in the event that data is missing (depending on type). This clause is also triggered on a column in aNESTED PATH clause when the latter has no match and aNULL complemented row is produced for it.on empty takes one of the following values:

If used,on_error takes one of the following values with the corresponding result as shown here:

SpecifyingON ERROR beforeON EMPTY is nonstandard and deprecated in MySQL; trying to do so causes the server to issue a warning. Expect support for the nonstandard syntax to be removed in a future version of MySQL.

When a value saved to a column is truncated, such as saving 3.14159 in aDECIMAL(10,1) column, a warning is issued independently of anyON ERROR option. When multiple values are truncated in a single statement, the warning is issued only once.

When the expression and path passed to this function resolve to JSON null,JSON_TABLE() returns SQLNULL, in accordance with the SQL standard, as shown here:

mysql> SELECT *    ->   FROM    ->     JSON_TABLE(    ->       '[ {"c1": null} ]',    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )    ->     ) as jt;+------+| c1   |+------+| NULL |+------+1 row in set (0.00 sec)

The following query demonstrates the use ofON EMPTY andON ERROR. The row corresponding to{"b":1} is empty for the path"$.a", and attempting to save[1,2] as a scalar produces an error; these rows are highlighted in the output shown.

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',    ->     "$[*]"    ->     COLUMNS(    ->       rowid FOR ORDINALITY,    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,    ->       bx INT EXISTS PATH "$.b"    ->     )    ->   ) AS tt;+-------+------+------------+------+| rowid | ac   | aj         | bx   |+-------+------+------------+------+|     1 | 3    | "3"        |    0 ||     2 | 2    | 2          |    0 ||     3 | 111  | {"x": 333} |    1 ||     4 | 0    | 0          |    0 ||     5 | 999  | [1, 2]     |    0 |+-------+------+------------+------+5 rows in set (0.00 sec)

Column names are subject to the usual rules and limitations governing table column names. SeeSection 11.2, “Schema Object Names”.

All JSON and JSON path expressions are checked for validity; an invalid expression of either type causes an error.

Each match for thepath preceding theCOLUMNS keyword maps to an individual row in the result table. For example, the following query gives the result shown here:

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',    ->     "$[*]" COLUMNS(    ->       xval VARCHAR(100) PATH "$.x",    ->       yval VARCHAR(100) PATH "$.y"    ->     )    ->   ) AS  jt1;+------+------+| xval | yval |+------+------+| 2    | 8    || 3    | 7    || 4    | 6    |+------+------+

The expression"$[*]" matches each element of the array. You can filter the rows in the result by modifying the path. For example, using"$[1]" limits extraction to the second element of the JSON array used as the source, as shown here:

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',    ->     "$[1]" COLUMNS(    ->       xval VARCHAR(100) PATH "$.x",    ->       yval VARCHAR(100) PATH "$.y"    ->     )    ->   ) AS  jt1;+------+------+| xval | yval |+------+------+| 3    | 7    |+------+------+

Within a column definition,"$" passes the entire match to the column;"$.x" and"$.y" pass only the values corresponding to the keysx andy, respectively, within that match. For more information, seeJSON Path Syntax.

NESTED PATH (or simplyNESTED;PATH is optional) produces a set of records for each match in theCOLUMNS clause to which it belongs. If there is no match, all columns of the nested path are set toNULL. This implements an outer join between the topmost clause andNESTED [PATH]. An inner join can be emulated by applying a suitable condition in theWHERE clause, as shown here:

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',    ->     '$[*]' COLUMNS(    ->             a INT PATH '$.a',    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')    ->            )    ->    ) AS jt    -> WHERE b IS NOT NULL;+------+------+| a    | b    |+------+------+|    1 |   11 ||    1 |  111 ||    2 |   22 ||    2 |  222 |+------+------+

Sibling nested paths—that is, two or more instances ofNESTED [PATH] in the sameCOLUMNS clause—are processed one after another, one at a time. While one nested path is producing records, columns of any sibling nested path expressions are set toNULL. This means that the total number of records for a single match within a single containingCOLUMNS clause is the sum and not the product of all records produced byNESTED [PATH] modifiers, as shown here:

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',    ->     '$[*]' COLUMNS(    ->         a INT PATH '$.a',    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')    ->     )    -> ) AS jt;+------+------+------+| a    | b1   | b2   |+------+------+------+|    1 |   11 | NULL ||    1 |  111 | NULL ||    1 | NULL |   11 ||    1 | NULL |  111 ||    2 |   22 | NULL ||    2 |  222 | NULL ||    2 | NULL |   22 ||    2 | NULL |  222 |+------+------+------+

AFOR ORDINALITY column enumerates records produced by theCOLUMNS clause, and can be used to distinguish parent records of a nested path, especially if values in parent records are the same, as can be seen here:

mysql> SELECT *    -> FROM    ->   JSON_TABLE(    ->     '[{"a": "a_val",    '>       "b": [{"c": "c_val", "l": [1,2]}]},    '>     {"a": "a_val",    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',    ->     '$[*]' COLUMNS(    ->       top_ord FOR ORDINALITY,    ->       apath VARCHAR(10) PATH '$.a',    ->       NESTED PATH '$.b[*]' COLUMNS (    ->         bpath VARCHAR(10) PATH '$.c',    ->         ord FOR ORDINALITY,    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')    ->         )    ->     )    -> ) as jt;+---------+---------+---------+------+-------+| top_ord | apath   | bpath   | ord  | lpath |+---------+---------+---------+------+-------+|       1 |  a_val  |  c_val  |    1 | 1     ||       1 |  a_val  |  c_val  |    1 | 2     ||       2 |  a_val  |  c_val  |    1 | 11    ||       2 |  a_val  |  c_val  |    2 | 22    |+---------+---------+---------+------+-------+

The source document contains an array of two elements; each of these elements produces two rows. The values ofapath andbpath are the same over the entire result set; this means that they cannot be used to determine whetherlpath values came from the same or different parents. The value of theord column remains the same as the set of records havingtop_ord equal to 1, so these two values are from a single object. The remaining two values are from different objects, since they have different values in theord column.

Normally, you cannot join a derived table which depends on columns of preceding tables in the sameFROM clause. MySQL, per the SQL standard, makes an exception for table functions; these are considered lateral derived tables. This is implicit, and for this reason is not allowed beforeJSON_TABLE(), also according to the standard.

Suppose you have a tablet1 created and populated using the statements shown here:

CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);INSERT INTO t1 () VALUESROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111));

You can then execute joins, such as this one, in whichJSON_TABLE() acts as a derived table while at the same time it refers to a column in a previously referenced table:

SELECT c1, c2, JSON_EXTRACT(c3, '$.*') FROM t1 AS m JOIN JSON_TABLE(  m.c3,   '$.*'   COLUMNS(    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,     bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,     ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY  )) AS ttON m.c1 > tt.at;

Attempting to use theLATERAL keyword with this query raisesER_PARSE_ERROR.