Menu

MySQL JSON_TABLE(): Syntax, ON EMPTY, and Examples

Updated on

MySQL JSON_TABLE() turns a JSON document into a relational table. Use DEFAULT 'false' ON EMPTY to return false when a column path has no match; the default string must contain a valid JSON value.

SELECT *
FROM JSON_TABLE(
    '[{"enabled": true}, {}, {"enabled": null}]',
    '$[*]' COLUMNS (
        id FOR ORDINALITY,
        enabled BOOLEAN PATH '$.enabled' DEFAULT 'false' ON EMPTY NULL ON ERROR
    )
) AS jt;

+----+---------+
| id | enabled |
+----+---------+
|  1 |       1 |
|  2 |       0 |
|  3 |    NULL |
+----+---------+

The JSON null in the third object becomes SQL NULL in MySQL 8.0.21 and later. Earlier MySQL 8.0 releases raise an error when the expression and path resolve to JSON null; see the MySQL 8.0.21 release notes.

ON EMPTY applies when the path has no match (row 2). ON ERROR applies when a matched value cannot be stored in the declared SQL type. A JSON null becomes SQL NULL; it does not trigger ON EMPTY. If omitted, both clauses default to NULL. Write ON EMPTY before ON ERROR.

JSON_TABLE() Syntax

Here is the syntax of the MySQL JSON_TABLE() function:

JSON_TABLE(expr, path COLUMNS (column[, column[, ...]])) [AS] alias

column:
    name FOR ORDINALITY
    |  name type PATH string_path [on_empty] [on_error]
    |  name type EXISTS PATH string_path
    |  NESTED [PATH] path COLUMNS (column[, column[, ...]])

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

Parameters

  • expr

    Required. An expression that returns the JSON document, such as a JSON value, column, or function call.

  • path

    Required. The path expression indicating the data to extract.

  • column

    Required. Define a column in the result table. You can define a column using the following 4 ways:

    • name FOR ORDINALITY: Generate a 1-based row counter named name.
    • name type PATH string_path [on_empty] [on_error]: Place the value specified by string_path into the column named name.
    • name type EXISTS PATH string_path: Place 1 or 0 in the column named name depending on whether there is a value at the path string_path .
    • NESTED [PATH] path COLUMNS (column[, column[, ...]]): Flatten the data in an embedded object or array into one or more rows.

  • {NULL | ERROR | DEFAULT json_string} ON EMPTY

    Optional. If specified, it determines how to handle a missing path match. A DEFAULT value is parsed as JSON, then converted to the declared SQL column type:

    • NULL ON EMPTY: If the specified path has no data, the JSON_TABLE() function will use NULL. It is the default behavior.
    • DEFAULT json_string ON EMPTY: If the specified path has no match, the JSON_TABLE() function uses the JSON value in json_string.
    • ERROR ON EMPTY: If the specified path has no data, the JSON_TABLE() function will throw an error.

  • {NULL | ERROR | DEFAULT json_string} ON ERROR

    Optional. If specified, it determines what to do when a matched value cannot be stored in the declared SQL type, such as an array in a scalar column:

    • NULL ON ERROR: The JSON_TABLE() function will use NULL if there is an error. It is the default behavior.
    • DEFAULT json_string ON ERROR: If an error occurs, the JSON_TABLE() function uses the JSON value in json_string.
    • ERROR ON ERROR: If there is an error, the JSON_TABLE() function will throw an error.

Truncation is handled separately from ON ERROR. For example, saving 3.14159 to a DECIMAL(10,1) column produces a warning regardless of the ON ERROR clause. See MySQL’s JSON_TABLE conversion rules.

Return value

JSON_TABLE() returns a table expression that can be used in the FROM clause. An alias is required.

MySQL raises an error in these cases:

  • The expr argument is not a valid JSON document. Use JSON_VALID() to check a document’s syntax.
  • The row-source or column path is not a valid JSON path expression.

JSON_TABLE() Examples

Simple example

In this example, each object element in the array is converted to a row in a relational table. The columns in the relational table correspond to the members in the object.

SELECT *
FROM
JSON_TABLE(
    '[{"x": 10, "y": 11}, {"x": 20, "y": 21}]',
    '$[*]'
    COLUMNS (
        id FOR ORDINALITY,
        x INT PATH '$.x',
        y INT PATH '$.y'
    )
) AS t;

+------+------+------+
| id   | x    | y    |
+------+------+------+
|    1 |   10 |   11 |
|    2 |   20 |   21 |
+------+------+------+

Here, there are two objects in the JSON array: {"x": 10, "y": 11} and {"x": 20, "y": 21}。

The path expression $[*] represents each element in the array, that is, the two objects in the array.

The COLUMNS clause defines 3 columns for the result table:

  • id FOR ORDINALITY: The id column is a row counter starting from 1.
  • x INT PATH '$.x: The x column corresponds to the member x in the object.
  • y INT PATH '$.y: The y column corresponds to the member y in the object.

Default value

SELECT *
FROM
JSON_TABLE(
    '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
    '$[*]'
    COLUMNS (
        id FOR ORDINALITY,
        x INT PATH '$.x' DEFAULT '100' ON EMPTY,
        y INT PATH '$.y'
    )
) AS t;

+------+------+------+
| id   | x    | y    |
+------+------+------+
|    1 |   10 |   11 |
|    2 |  100 |   21 |
|    3 |   30 | NULL |
+------+------+------+

Here, notice the definition of column x on the following line:

x INT PATH '$.x' DEFAULT '100' ON EMPTY,

DEFAULT '100' ON EMPTY defines the default value 100 to be used when the member x does not exist or the value of x is empty.

Extract the specified member

This statement extracts the second element of an JSON array.

SELECT *
FROM
JSON_TABLE(
    '[{"x":10,"y":11}, {"y": 21}, {"x": 30}]',
    '$[1]'
    COLUMNS (
        x INT PATH '$.x',
        y INT PATH '$.y'
    )
) AS t;

+------+------+
| x    | y    |
+------+------+
| NULL |   21 |
+------+------+

Here, the path expression $[1] tells JSON_TABLE() to extract only the second element in the JSON array, {"y": 21}, so the SELECT statement returns one row.

Flattens nested arrays

SELECT *
FROM
JSON_TABLE(
    '[{"x":10,"y":[11, 12]}, {"x":20,"y":[21, 22]}]',
    '$[*]'
    COLUMNS (
        x INT PATH '$.x',
        NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')
    )
) AS t;

+------+------+
| x    | y    |
+------+------+
|   10 |   11 |
|   10 |   12 |
|   20 |   21 |
|   20 |   22 |
+------+------+

Here, the member y in each object is an array. This column definition expands each array:

NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$')

This uses the NESTED PATH '$.y[*]' clause to expand the array in member y and place each element in the array into a column named y.

Because each array contains two elements, each parent object produces two rows in the relational table.

Nested objects

SELECT *
FROM
JSON_TABLE(
    '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]',
    '$[*]'
    COLUMNS (
        x INT PATH '$.x',
        NESTED PATH '$.y' COLUMNS (
            ya INT PATH '$.a',
            yb INT PATH '$.b'
        )
    )
) AS t;

+------+------+------+
| x    | ya   | yb   |
+------+------+------+
|   10 |   11 |   12 |
|   20 |   21 |   22 |
+------+------+------+

Here, the NESTED PATH '$.y' clause expands the object at y into two columns:

  • The member a is extracted into the column ya.
  • The member b is extracted into the column yb.

When a nested path has no match

NESTED PATH keeps the parent row when its path has no match. The nested columns are set to NULL by default, so an empty or missing array does not remove the parent object:

SELECT *
FROM JSON_TABLE(
    '[{"id": 1, "items": [{"sku": "A"}, {"sku": "B"}]}, {"id": 2, "items": []}, {"id": 3}]',
    '$[*]' COLUMNS (
        id INT PATH '$.id',
        NESTED PATH '$.items[*]' COLUMNS (
            sku VARCHAR(10) PATH '$.sku'
        )
    )
) AS jt;

+----+------+
| id | sku  |
+----+------+
|  1 | A    |
|  1 | B    |
|  2 | NULL |
|  3 | NULL |
+----+------+

This is outer-join behavior. A WHERE condition on a required nested value can remove the NULL-complemented rows; use a value that cannot legitimately be NULL if you need to distinguish “no nested match” from a matched JSON null. An ON EMPTY clause on a nested column can supply a default for a missing nested value.

Sibling NESTED PATH clauses in the same COLUMNS list are processed one after another, not as a Cartesian product. While one sibling produces rows, the other sibling’s columns are NULL; the total rows are the sum of their matches. See MySQL’s JSON_TABLE reference for an example.