MySQL JSON_TABLE() Function

In MySQL, the JSON_TABLE() function extracts data from a specified JSON document and returns a relational table with the specified columns.

`JSON_TABLE()` Syntax

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

JSON_TABLE(
    json,
    path COLUMNS (column[, column[, ...]])
)

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

json

Required. The JSON document.
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 counter column 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 value} ON EMPTY

Optional. If specified, it determines how to handle empty data:
- NULL ON EMPTY: If the specified path has no data, the JSON_TABLE() function will use NULL. It is the default behavior.
- DEFAULT value ON EMPTY: If the specified path has no data, the JSON_TABLE() function will use value.
- ERROR ON EMPTY: If the specified path has no data, the JSON_TABLE() function will throw an error.
{NULL | ERROR | DEFAULT value} ON ERROR

Optional. If specified, it determines how to handle errors:
- NULL ON ERROR: The JSON_TABLE() function will use NULL if there is an error. It is the default behavior.
- DEFAULT value ON ERROR: The JSON_TABLE() function will use value if there is an error.
- ERROR ON ERROR: If there is an error, the JSON_TABLE() function will throw an error.

Return value

The MySQL JSON_TABLE() function extracts data from a specified JSON document and returns a relational table with the specified columns. You can use the table returned by JSON_TABLE() as an regular table.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.

`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 an auto-incrementing sequence 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] tell JSON_TABLE() to extract only the 2nd element in the JSON array, that is {"y": 21}. So the SELECT statement only 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 of the objects are arrays. Notice the following column definition:

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.

Since there are 2 elements in each array , so one array be translated to 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 member y of the object into 2 columns:

The member a are extracted to the column ya
The member b are extracted to the column yb

JSON_TABLE() Syntax