MariaDB JSON_TABLE() Function

In MariaDB, JSON_TABLE() is a built-in function that extracts data from a specified JSON document and returns a relational table with specified columns. This function was introduced in MariaDB 10.6.0.

MariaDB JSON_TABLE() Syntax

Here is the syntax for the MariaDB 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 MariaDB 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 from JSON_TABLE() like a normal table.

MariaDB JSON_TABLE() Examples

Here are some common examples showing basic usages of the MariaDB JSON_QUOTE() function.

simple example

In this example, each object element in the array is converted to each row in a relational table. The columns in the relational table correspond to the members in each 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;

Output:

+------+------+------+
| 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 in the relational 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.

$ in the $.x and $.y represent the current context object, that is, each object in the array.

Defaults

MariaDB JSON_TABLE() allows you to specify default values ​​for those empty column values.

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;

Output:

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

Here, notice the column definition 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

MariaDB JSON_TABLE() allows you to extract only JSON members specified by expressions.

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

Output:

+------+------+
| 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

MariaDB JSON_TABLE() allows you to extract nested JSON array members using the NESTED clause.

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;

Output:

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

Here, the member of the object in the array y is an array, note 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.

Flatten embedded objects

MariaDB JSON_TABLE() allows you to extract embedded JSON object members using the NESTED clause.

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;

Output:

+------+------+------+
| 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

Conclusion

In MariaDB, JSON_TABLE() is a built-in function that extracts data from a specified JSON document and returns a relational table with specified columns.