JSON_TABLE()

This page is part of MariaDB's Documentation.

The parent of this page is: Functions for MariaDB Enterprise Server

Overview

In 10.6 ES, 10.6 CS:: A table function that creates relational data in the form of a table consisting of rows and columns, from JSON data.

In 10.6 ES:: A table function that creates relational data in the form of a table consisting of rows and columns, from JSON data.

In 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:: Not present

In 10.5 ES, 10.4 ES:: Not present

USAGE

JSON_TABLE(
 json_data,
 path COLUMNS(columns_list)
) [AS] alias

columns_list:
   column [, column[, ... ]]

column:
     name FOR ORDINALITY
   | name type PATH path_str [on_empty_on_error]
   | name type EXISTS PATH path_str [on_empty_on_error]
   | NESTED PATH path COLUMNS (columns_list)

on_empty_on_error:
   [ behavior ON EMPTY ] [ behavior ON ERROR ]

behavior:
   ERROR | NULL | DEFAULT <value_expression>

DETAILS

The JSON_TABLE() function is the first "table function" in MariaDB Server. A table function contains rows and columns like a relational table, but the rows and columns are not stored in a relational table. When the table function is called, it generates the rows and columns based on the input.
JSON_TABLE() is a table function that transforms JSON data into a relational table, so that JSON data can be queried and joined with regular tables.
JSON_TABLE() can be used in SELECT statements by calling it in the FROM or JOIN clauses. It can also be used in a nested SELECT within another statement, such as CREATE TABLE .. AS SELECT, INSERT INTO .. SELECT, and DELETE FROM ... WHERE ... SELECT.
JSON_TABLE() can be used in the multi-table syntax of the DELETE and UPDATE statements.
JSON_TABLE() requires a table alias.
JSON_TABLE() uses the utf8mb4 character set by default for columns that use string data types, such as CHAR and VARCHAR. A different character set can be specified for a column using the same syntax used for columns in regular tables.
When the JSON data is NULL, JSON_TABLE() returns an empty set.
When JSON_TABLE() parses a column's value from JSON that is too big to fit into the column's data type, the value can be truncated.
By default, JSON_TABLE() implicitly sets NULL ON EMPTY and NULL ON ERROR for columns, which means that the column's value is set to NULL if its value cannot be found or if an error occurs while parsing its value.

SYNONYMS

SCHEMA

PARAMETERS

Parameter	Description
`json_data`	The value expression of JSON data can be provided in many forms, such as a column, an expression comprised of multiple columns, a user variable, or a string literal.
`path`	The JSON Path expression for each row. The JSON Path expression produces an SQL/JSON sequence with one SQL/JSON item for each row in the output table.

CLAUSES

The different clauses in the function syntax are described as follows:

Clause (link to example)	Description
COLUMNS	Defines columns. Three types of columns can be defined: ordinality columns, regular columns, and nested columns. Column types are described in more detail in the subsequent table.
FOR ORDINALITY	Specifies an ordinality column. Ordinality columns provide sequential numbering of rows starting at 1
PATH	Specifies a regular column of the specified scalar type. Regular columns require a JSON Path expression for the column, which is used to search for the column within the current SQL/JSON item produced by the JSON Path expression for the row. Also supports optional ON EMPTY and ON ERROR clauses.
EXISTS PATH	Tests the JSON Path expression provided. It returns `1` if data exists at the JSON Path or `0` if data doesn't exist at the JSON Path.
NESTED PATH	Specifies nested columns defined by the specified nested JSON Path. Nested JSON Paths can be nested to multi-levels.
ON EMPTY	Specifies behavior if the result of a JSON Path expression is empty.
ON ERROR	Specifies behavior if an error condition happens.

JSON PATHS

The JSON_TABLE() function requires a JSON Path to be provided in multiple places:

The function requires a JSON Path for each row to be provided as the path parameter.
The function requires a JSON Path for each regular column to be provided with the PATH clause.
The function requires a JSON Path for each nested column to be provided with the NESTED PATH clause.
The function requires a JSON Path to be provided with the EXISTS PATH clause when checking for the existence of column values.

Some examples of JSON Paths are:

JSON Path	Description
`$`	Matches the root JSON data
`$[*]`	Matches every item in the root JSON Array or auto-promoted single-item array
`$[0]`	Matches the first item in the root JSON Array or auto-promoted single-item array Larger index numbers from 1..N index further into the array If the index number is beyond the end of the array, a NULL is matched
`$.*`	Matches every member of the root JSON Object
`$.x`	Matches the member of the root JSON Object with key `x`
`$.x[0]`	The member of the root JSON Object with key `x` is treated as a JSON Array (perhaps promoting it into a single-item array) and its first item is matched Larger index numbers from 1..N index further into the array If the index number is beyond the end of the array, a NULL is matched

COLUMN TYPES

The different types of columns in the syntax are described as follows:

Column type	Description
name FOR ORDINALITY	Creates ordinality columns, and enumerates rows in the `COLUMNS` clause using counter "name" of type `UNSIGNED INT` with initial value `1`
name type PATH path_str [on_empty_on_error]	Creates a regular column using name, data type, JSON Path expression, and an optional handler if result is empty, or an error condition.
name type EXISTS PATH path_str [on_empty_on_error]	Creates a regular column that has a value of either `1` or `0`, depending on if JSON data exists at the specified JSON Path, using name, data type, JSON Path expression, and an optional handler if result is empty, or an error condition.
NESTED PATH path COLUMNS (columns_list)	Creates nested columns using the specified nested JSON Path expression.

ON EMPTY

The ON EMPTY handler is described as follows:

Clause (link to example)	Description
NULL ON EMPTY	Sets the column to `NULL` when the column's value is missing from the JSON data.
DEFAULT <value_expression> ON EMPTY	Sets the column to the specified value expression when the column's value is missing from the JSON data.
ERROR ON EMPTY	Throws an error when the column's value is missing from the JSON data.

ON ERROR

The ON ERROR handler is described as follows:

Clause (link to example)	Description
NULL ON ERROR	Sets the column to `NULL` when an error occurs.
DEFAULT <value_expression> ON ERROR	Sets the column to the specified value expression when an error occurs.
ERROR ON ERROR	Throws an error when an error occurs.

SKYSQL

PRIVILEGES

With Literal Values

In the following example, JSON_TABLE() is called with a JSON literal value. The result demonstrates how the JSON is transformed into a a table result with 2 rows of 3 columns:

SELECT *
FROM JSON_TABLE(
    '[ {"id": 42, "name1": "John", "name2": "Smith"},
       {"id": 99, "name1": ["Jane", "S."], "name2": "Doe"} ]',
    '$[*]'
    COLUMNS(
      id INT PATH '$.id',
      first_name VARCHAR(10) PATH '$.name1[0]',
      last_name  VARCHAR(10) PATH '$.name2'
    )
) AS people;

+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|   42 | John       | Smith     |
|   99 | Jane       | Doe       |
+------+------------+-----------+

Example Schema and Data

Many of the examples use the test database:

CREATE DATABASE IF NOT EXISTS test;

Many of the examples use the test.contacts table:

CREATE TABLE test.contacts (
  contact JSON
);

INSERT INTO test.contacts
    VALUES
    ('{"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"}'),
    ('{"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"}'),
    ('{"email": "johnny.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}');

Per Row Extraction

In this example, JSON_TABLE() queries the test.contacts table. It matches each row using the JSON Path expression specified as the path parameter. In this case, the JSON Path expression for each row is $, which refers to the root of the JSON data. It matches each column using the JSON Path expression specified in the COLUMNS clause. In the case of the first_name column, the JSON Path expression for the column is $.first_name, which refers to the JSON Object with the key named first_name.

SELECT *
FROM contacts
JOIN JSON_TABLE(
    contacts.contact,
    '$'
    COLUMNS(
      first_name VARCHAR(10) PATH '$.first_name',
      last_name  VARCHAR(10) PATH '$.last_name',
      email VARCHAR(50) PATH '$.email' NULL ON EMPTY NULL ON ERROR,
      email_exists INT EXISTS PATH '$.email',
      phone VARCHAR(50) PATH '$.phone' NULL ON EMPTY NULL ON ERROR,
      phone_exists INT EXISTS PATH '$.phone'
    )
) AS contacts_json\G

*************************** 1. row ***************************
     contact: {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"}
  first_name: John
   last_name: Smith
       email: john.smith@example.com
email_exists: 1
       phone: NULL
phone_exists: 0
*************************** 2. row ***************************
     contact: {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"}
  first_name: Jon
   last_name: Smith
       email: jon.smith@example.com
email_exists: 1
       phone: NULL
phone_exists: 0
*************************** 3. row ***************************
     contact: {"email": "johnny.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}
  first_name: Johnny
   last_name: Smith
       email: johnny.smith@example.com
email_exists: 1
       phone: NULL
phone_exists: 0

Ordinality Columns

In the following example, JSON_TABLE() defines an ordinality column. Since the JSON Path is '$[*]', the function returns a row for each item in the JSON array. Each row is identified by an ordinality column that sequentially numbers the rows starting with 1.

SELECT *
FROM JSON_TABLE(
    '[
      {"first_name": "John", "last_name": "Smith"},
      {"first_name": "Jon", "last_name": "Smith"},
      {"first_name": "Johnny", "last_name": "Smith"}
     ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name'
    )
) AS people;

+-------+------------+-----------+
| rowid | first_name | last_name |
+-------+------------+-----------+
|     1 | John       | Smith     |
|     2 | Jon        | Smith     |
|     3 | Johnny     | Smith     |
+-------+------------+-----------+

Use Nested Path

The JSON_TABLE() function supports nested paths using the NESTED PATH clause, followed by a nested COLUMNS() clause.

A nested path must have at least one column defined in its nested COLUMNS() clause.

Two types of nested paths are supported:

Hierarchical nested paths use multiple NESTED PATH clauses in the COLUMNS() clauses at different levels
Sibling nested paths use multiple NESTED PATH clauses in the same COLUMNS() clause at the same level

Nested paths in the root COLUMNS() clause are relative to the root JSON path. Nested paths in nested COLUMNS() clauses are relative to the preceding nested path in the hierarchy.

Each nested path can have its own ordinality column defined in its nested COLUMNS() clause. Ordinality columns can be used with nested paths to maintain details about the structure and hierarchy of the original JSON data.

In the following example, JSON_TABLE() is used in a query that contains both hierarchical and sibling nested paths:

SELECT rowid,
    rowid_email, email_type, email_address,
    rowid_family,
    rowid_email_family, email_type_family, email_address_family
FROM JSON_TABLE(
   '[
      {"first_name": "John", "last_name": "Smith",
          "email": [
              {"email_type": "home", "email_address": "john.smith@example.com"},
              {"email_type": "home", "email_address":"johnsmith@example.com"}
          ],
          "family": [
              {"relationship": "spouse", "first_name": "Jane", "last_name": "Smith",
                  "email": [
                      {"email_type": "home", "email_address": "jane.smith@example.com"},
                      {"email_type": "home", "email_address":"janesmith@example.com"}
                  ]
              },
             {"relationship": "child", "first_name": "Jon", "last_name": "Smith",
                 "email": [
                     {"email_type": "home", "email_address": "jon.smith@example.com"},
                     {"email_type": "home", "email_address":"jonsmith@example.com"}
                 ]
             }
         ]
      }
    ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(100) PATH '$.first_name',
        last_name VARCHAR(100) PATH '$.last_name',
        NESTED PATH '$.email[*]'
        COLUMNS (
            rowid_email FOR ORDINALITY,
            email_type VARCHAR(25) PATH '$.email_type',
            email_address VARCHAR(100) PATH '$.email_address'
        ),
        NESTED PATH '$.family[*]'
        COLUMNS (
            rowid_family FOR ORDINALITY,
            relationship VARCHAR(25) PATH '$.relationship',
            first_name_family VARCHAR(100) PATH '$.first_name',
            last_name_family VARCHAR(100) PATH '$.last_name',
            NESTED PATH '$.email[*]'
            COLUMNS (
                rowid_email_family FOR ORDINALITY,
                email_type_family VARCHAR(25) PATH '$.email_type',
                email_address_family VARCHAR(100) PATH '$.email_address'
            )
        )
    )
) AS families;

+-------+-------------+------------+------------------------+--------------+--------------------+-------------------+------------------------+
| rowid | rowid_email | email_type | email_address          | rowid_family | rowid_email_family | email_type_family | email_address_family   |
+-------+-------------+------------+------------------------+--------------+--------------------+-------------------+------------------------+
|     1 |           1 | home       | john.smith@example.com |         NULL |               NULL | NULL              | NULL                   |
|     1 |           2 | home       | johnsmith@example.com  |         NULL |               NULL | NULL              | NULL                   |
|     1 |        NULL | NULL       | NULL                   |            1 |                  1 | home              | jane.smith@example.com |
|     1 |        NULL | NULL       | NULL                   |            1 |                  2 | home              | janesmith@example.com  |
|     1 |        NULL | NULL       | NULL                   |            2 |                  1 | home              | jon.smith@example.com  |
|     1 |        NULL | NULL       | NULL                   |            2 |                  2 | home              | jonsmith@example.com   |
+-------+-------------+------------+------------------------+--------------+--------------------+-------------------+------------------------+

The query contains both hierarchical and sibling nested paths:

The '$.email[*]' and '$.family[*]' nested paths are both in the root COLUMNS() clause, so they are examples of sibling nested paths. With sibling nested paths, the NESTED PATH clauses are processed sequentially and are returned as separate rows. The output is processed similarly to an OUTER JOIN. When one nested path is being processed, the columns of any sibling nested path expressions are set to NULL.
The '$.family[*]' nested path contains a hierarchical nested path for '$.email[*]'.

The ordinality columns at each level of nesting maintain details about the structure:

The ordinality column defined in the root COLUMNS() clause, which is rowid in the above example, has the same value for every distinct element generated from the root JSON path.
The ordinality columns defined in the COLUMNS() clauses for sibling nested paths, which are rowid_email and rowid_family in the above example, are useful for showing which elements represent siblings. When two rows have the same rowid value, but one row sets rowid_email and the other row sets rowid_family, they represent two sibling elements.
The ordinality columns defined in the COLUMNS() clauses for hierarchical nested paths, which are rowid_family and rowid_email_family in the above example, are useful for showing which elements correspond in the hierarchy. When two rows have the same rowid_family value, but they have different rowid_email_family values, they represent two elements with the same parent in the hierarchy.

Use with CREATE TABLE AS SELECT

The JSON_TABLE() function can be used to create a new relational table using a CREATE TABLE ... AS SELECT statement:

CREATE TABLE test.t1 AS
    SELECT *
    FROM JSON_TABLE(
        '[
             {"first_name": "John", "last_name": "Smith"},
             {"first_name": "Jon", "last_name": "Smith"},
             {"first_name": "Johnny", "last_name": "Smith"}
         ]',
        '$[*]'
        COLUMNS(
            rowid FOR ORDINALITY,
            first_name VARCHAR(10) PATH '$.first_name',
            last_name  VARCHAR(10) PATH '$.last_name'
        )
    ) AS people;

Verify that a new table test.t1 gets created using a SHOW CREATE TABLE statement:

SHOW CREATE TABLE test.t1\G

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `rowid` int(11) DEFAULT NULL,
  `first_name` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `last_name` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Verify that the new table test.t1 has data from the JSON used in the JSON_TABLE() function:

SELECT * FROM test.t1;

+-------+------------+-----------+
| rowid | first_name | last_name |
+-------+------------+-----------+
|     1 | John       | Smith     |
|     2 | Jon        | Smith     |
|     3 | Johnny     | Smith     |
+-------+------------+-----------+

Use with CREATE VIEW

The JSON_TABLE() function can be used to create a view using a CREATE VIEW statement:

CREATE VIEW test.v1 AS
    SELECT *
    FROM JSON_TABLE(
        '[
             {"last_name": "Smith", "first_name": "John"},
             {"last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]',
        '$[*]'
        COLUMNS(
            rowid FOR ORDINALITY,
            first_name VARCHAR(10) PATH '$.first_name',
            last_name  VARCHAR(10) PATH '$.last_name'
        )
    ) AS people;

Verify that a new view test.v1 gets created using a SHOW CREATE VIEW statement:

SHOW CREATE VIEW test.v1\G

*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`tester`@`%` SQL SECURITY DEFINER VIEW `v1` AS select `people`.`rowid` AS `rowid`,`people`.`first_name` AS `first_name`,`people`.`last_name` AS `last_name` from JSON_TABLE('[\n             {"last_name": "Smith", "first_name": "John"},\n             {"last_name": "Smith", "first_name": "Jon"},\n             {"last_name": "Smith", "first_name": "Johnny"}\n         ]', '$[*]' COLUMNS (`rowid` FOR ORDINALITY, `first_name` varchar(10) PATH '$.first_name', `last_name` varchar(10) PATH '$.last_name')) `people`
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci

Verify that the new view test.v1 has data from the JSON used in the JSON_TABLE() function:

SELECT * FROM test.v1;

+-------+------------+-----------+
| rowid | first_name | last_name |
+-------+------------+-----------+
|     1 | John       | Smith     |
|     2 | Jon        | Smith     |
|     3 | Johnny     | Smith     |
+-------+------------+-----------+

Use with INSERT INTO SELECT

The JSON_TABLE() function can be used to insert data into an existing relational table using an INSERT INTO ... SELECT statement:

CREATE TABLE test.t2 (
  rowid INT DEFAULT NULL,
  first_name VARCHAR(10) DEFAULT NULL,
  last_name VARCHAR(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test.t2
    SELECT *
    FROM JSON_TABLE(
        '[
             {"first_name": "John", "last_name": "Smith"},
             {"first_name": "Jon", "last_name": "Smith"},
             {"first_name": "Johnny", "last_name": "Smith"}
         ]',
        '$[*]'
        COLUMNS (
            rowid FOR ORDINALITY,
            first_name VARCHAR(10) PATH '$.first_name',
            last_name  VARCHAR(10) PATH '$.last_name'
        )
    ) AS people;

Verify that the table test.t2 has the inserted data:

SELECT * FROM test.t2;

+-------+------------+-----------+
| rowid | first_name | last_name |
+-------+------------+-----------+
|     1 | John       | Smith     |
|     2 | Jon        | Smith     |
|     3 | Johnny     | Smith     |
+-------+------------+-----------+

Use with UPDATE

The JSON_TABLE() function can be used to update data in an existing relational table using an UPDATE statement:

CREATE TABLE test.t3 (
  id INT PRIMARY KEY,
  first_name VARCHAR(10) DEFAULT NULL,
  last_name VARCHAR(10) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test.t3 (id, first_name, last_name) VALUES
    (1, 'John', 'Smith'),
    (2, 'Jon', 'Smith'),
    (3, 'Johnny', 'Smith');

UPDATE test.t3,
    JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": "johnny.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}
         ]',
        '$[*]'
        COLUMNS (
            first_name VARCHAR(10) PATH '$.first_name',
            last_name  VARCHAR(10) PATH '$.last_name',
            email VARCHAR(50) PATH '$.email'
        )
    ) AS people
    SET t3.email = people.email
    WHERE t3.first_name = people.first_name
        AND t3.last_name = people.last_name;

SELECT * FROM test.t3;

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | John       | Smith     | john.smith@example.com   |
|  2 | Jon        | Smith     | jon.smith@example.com    |
|  3 | Johnny     | Smith     | johnny.smith@example.com |
+----+------------+-----------+--------------------------+

Use with DELETE

The JSON_TABLE() function can be used to delete data in an existing relational table using a DELETE statement:

CREATE TABLE test.t4 (
  id INT PRIMARY KEY,
  first_name VARCHAR(10) DEFAULT NULL,
  last_name VARCHAR(10) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test.t4 (id, first_name, last_name, email) VALUES
    (1, 'John', 'Smith', 'john.smith@example.com'),
    (2, 'Jon', 'Smith', 'jon.smith@example.com'),
    (3, 'Johnny', 'Smith', 'johnny.smith@example.com');

DELETE FROM test.t4
    WHERE t4.email IN (
        SELECT email
        FROM JSON_TABLE(
           '[
                {"email": "johnny.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}
            ]',
           '$[*]'
           COLUMNS (
               first_name VARCHAR(10) PATH '$.first_name',
               last_name  VARCHAR(10) PATH '$.last_name',
               email VARCHAR(50) PATH '$.email'
           )
        ) AS people
    );

SELECT * FROM test.t4;

+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+

NULL ON EMPTY

The JSON_TABLE() function supports returning the NULL value using the NULL ON EMPTY clause if a specified JSON Path is missing:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' NULL ON EMPTY,
        last_name  VARCHAR(10) PATH '$.last_name' NULL ON EMPTY,
        email VARCHAR(50) PATH '$.email' NULL ON EMPTY
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | John       | Smith     | john.smith@example.com |
|     2 | Jon        | Smith     | jon.smith@example.com  |
|     3 | Johnny     | Smith     | NULL                   |
+-------+------------+-----------+------------------------+

ERROR ON EMPTY

The JSON_TABLE() function supports returning an error using the ERROR ON EMPTY clause if a specified JSON Path is missing:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' ERROR ON EMPTY,
        last_name  VARCHAR(10) PATH '$.last_name' ERROR ON EMPTY,
        email VARCHAR(50) PATH '$.email' ERROR ON EMPTY
    )
) AS people;

ERROR 4176 (HY000): Field 'email' can't be set for JSON_TABLE 'people'.

DEFAULT <value_expression> ON EMPTY

The JSON_TABLE() function supports returning a default value using the DEFAULT ... ON EMPTY clause if a specified JSON Path is missing:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' DEFAULT 'N/A' ON EMPTY,
        last_name  VARCHAR(10) PATH '$.last_name' DEFAULT 'N/A' ON EMPTY,
        email VARCHAR(50) PATH '$.email' DEFAULT 'N/A' ON EMPTY
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | John       | Smith     | john.smith@example.com |
|     2 | Jon        | Smith     | jon.smith@example.com  |
|     3 | Johnny     | Smith     | N/A                    |
+-------+------------+-----------+------------------------+

JOIN

The JSON_TABLE() function supports all join types.

When the JSON_TABLE() function's arguments reference a joined table, the JSON_TABLE() function must be evaluated after the dependent tables.

For example, the following query contains a LEFT JOIN:

SELECT contacts_json.*
FROM test.contacts
LEFT JOIN JSON_TABLE(
    contacts.contact,
    '$'
    COLUMNS(
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
) AS contacts_json
ON true;

+------------+-----------+--------------------------+
| first_name | last_name | email                    |
+------------+-----------+--------------------------+
| John       | Smith     | john.smith@example.com   |
| Jon        | Smith     | jon.smith@example.com    |
| Johnny     | Smith     | johnny.smith@example.com |
+------------+-----------+--------------------------+

In the above query, the JSON_TABLE() function call is dependent on the test.contacts table, because the contacts.contact column is provided as an argument to the JSON_TABLE() function. The function call's dependency is satisfied, because the test.contacts table precedes the JSON_TABLE() function call in the join order, so the function is evaluated successfully.

However, if the LEFT JOIN in the example is changed to a RIGHT JOIN, then the join order is reversed:

SELECT contacts_json.*
FROM test.contacts
RIGHT JOIN JSON_TABLE(
    contacts.contact,
    '$'
    COLUMNS(
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
) AS contacts_json
ON true;

ERROR 1054 (42S22): Unknown column 'contacts.contact' in 'JSON_TABLE argument'

In the above query, the function call's dependency is not satisfied, because the JSON_TABLE() function call precedes the test.contacts table in the join order, so the function fails with the ER_BAD_FIELD_ERROR error code.

ERROR HANDLING

NULL ON ERROR

When a column is defined with the NULL ON ERROR clause, the JSON_TABLE() function sets the column's value to NULL if an error condition occurs:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": ["john.smith@example.com"], "last_name": "Smith", "first_name": ["Johnny"]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' NULL ON ERROR,
        last_name  VARCHAR(10) PATH '$.last_name' NULL ON ERROR,
        email VARCHAR(50) PATH '$.email' NULL ON ERROR
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | John       | Smith     | john.smith@example.com |
|     2 | Jon        | Smith     | jon.smith@example.com  |
|     3 | NULL       | Smith     | NULL                   |
+-------+------------+-----------+------------------------+

ERROR ON ERROR

When a column is defined with the ERROR ON ERROR clause, the JSON_TABLE() function raises an error if an error condition occurs:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": ["john.smith@example.com"], "last_name": "Smith", "first_name": ["Johnny"]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' ERROR ON ERROR,
        last_name  VARCHAR(10) PATH '$.last_name' ERROR ON ERROR,
        email VARCHAR(50) PATH '$.email' ERROR ON ERROR
    )
) AS people;

ERROR 4178 (HY000): Can't store an array or an object in the scalar column 'first_name' of JSON_TABLE 'people'.

DEFAULT <value_expression> ON ERROR

When a column is defined with the DEFAULT <value_expression> ON ERROR clause, the JSON_TABLE() function sets the column's value to the specified default value if an error condition occurs:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": ["john.smith@example.com"], "last_name": "Smith", "first_name": ["Johnny"]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' DEFAULT 'N/A' ON ERROR,
        last_name  VARCHAR(10) PATH '$.last_name' DEFAULT 'N/A' ON ERROR,
        email VARCHAR(50) PATH '$.email' DEFAULT 'N/A' ON ERROR
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | John       | Smith     | john.smith@example.com |
|     2 | Jon        | Smith     | jon.smith@example.com  |
|     3 | N/A        | Smith     | N/A                    |
+-------+------------+-----------+------------------------+

Use Invalid JSON Syntax

When invalid JSON syntax is provided to the JSON_TABLE() function, the operation will fail with the ER_JSON_SYNTAX error:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' DEFAULT 'N/A' ON EMPTY,
        last_name  VARCHAR(10) PATH '$.last_name' DEFAULT 'N/A' ON EMPTY,
        email VARCHAR(50) PATH '$.email' DEFAULT 'N/A' ON EMPTY
    )
) AS people;

ERROR 4038 (HY000): Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 259

Set a Column Value to Non-Scalar

When JSON_TABLE() resolves the JSON path for a column to a non-scalar value, the behavior depends on how the column is defined.

By default, NULL ON ERROR is implicitly set for columns, so the operation will set the column's value to NULL:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": ["john.smith@example.com"], "last_name": "Smith", "first_name": ["Johnny"]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | John       | Smith     | john.smith@example.com |
|     2 | Jon        | Smith     | jon.smith@example.com  |
|     3 | NULL       | Smith     | NULL                   |
+-------+------------+-----------+------------------------+

When ERROR ON ERROR is set for the column, the operation will fail with the ER_JSON_TABLE_SCALAR_EXPECTED error:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": ["john.smith@example.com"], "last_name": "Smith", "first_name": ["Johnny"]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name' ERROR ON ERROR,
        last_name  VARCHAR(10) PATH '$.last_name' ERROR ON ERROR,
        email VARCHAR(50) PATH '$.email' ERROR ON ERROR
    )
) AS people;

ERROR 4178 (HY000): Can't store an array or an object in the scalar column 'first_name' of JSON_TABLE 'people'.

JSON Table Requires an Alias

If a user tries to use the JSON_TABLE() function without an alias, then the operation will fail with the ER_JSON_TABLE_ALIAS_REQUIRED error:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"last_name": "Smith", "first_name": "Johnny"}
         ]]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
);

ERROR 4177 (HY000): Every table function must have an alias.

Multiple Matches

When JSON_TABLE() resolves the JSON path for a column to a multiple values, the behavior depends on how the column is defined.

By default, NULL ON ERROR is implicitly set for columns, so the operation will set the column's value to NULL:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": [ "John", "Jonathan" ]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name[*]',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
) AS people;

+-------+------------+-----------+------------------------+
| rowid | first_name | last_name | email                  |
+-------+------------+-----------+------------------------+
|     1 | NULL       | Smith     | john.smith@example.com |
+-------+------------+-----------+------------------------+

When ERROR ON ERROR is set for the column, the operation will fail with the ER_JSON_TABLE_MULTIPLE_MATCHES error:

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": [ "John", "Jonathan" ]}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name[*]' ERROR ON ERROR,
        last_name  VARCHAR(10) PATH '$.last_name' ERROR ON ERROR,
        email VARCHAR(50) PATH '$.email' ERROR ON ERROR
    )
) AS people;

ERROR 4179 (HY000): Can't store multiple matches of the path in the column 'first_name' of JSON_TABLE 'people'.

FULLTEXT Indexes not Supported

If a user tries to use the JSON_TABLE() function with a MATCH(..) AGAINST(..), then the operation will fail with the ER_TABLE_CANT_HANDLE_FT error:

SELECT first_name, MATCH(first_name) AGAINST('John')
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH '$.first_name',
        last_name  VARCHAR(10) PATH '$.last_name',
        email VARCHAR(50) PATH '$.email'
    )
) AS people;

ERROR 1214 (HY000): The storage engine JSON_TABLE function doesn't support FULLTEXT indexes

FEATURE INTERACTION

sql_mode=ANSI_QUOTES

When sql_mode contains ANSI_QUOTES, double quotation marks (") are only allowed to quite identifiers. Therefore, when sql_mode contains ANSI_QUOTES, double quotation marks (") cannot be used to quote JSON literals or JSON Paths.

It should be noted that several sql_mode values implicitly set ANSI_QUOTES, including ANSI, DB2, MAXDB, MSSQL, ORACLE, and POSTGRESQL.

For example:

SET sql_mode=CONCAT(@@sql_mode, ',ORACLE');

SELECT *
FROM JSON_TABLE(
        '[
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "John"},
             {"email": "jon.smith@example.com", "last_name": "Smith", "first_name": "Jon"},
             {"email": "john.smith@example.com", "last_name": "Smith", "first_name": "Johnny"}
         ]',
    '$[*]'
    COLUMNS(
        rowid FOR ORDINALITY,
        first_name VARCHAR(10) PATH "$.first_name",
        last_name  VARCHAR(10) PATH "$.last_name",
        email VARCHAR(50) PATH "$.email"
    )
) AS people;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"$.first_name",
        last_name  VARCHAR(10) PATH "$.last_name",
        em...' at line 7

Statement-Based Replication

The JSON_TABLE() function is declared to be unsafe for statement-based replication (SBR), because it is is non-deterministic.

The JSON_TABLE() function computes JSON Paths in a streaming manner, and JSON object members are returned in the order they appear in the JSON object. Therefore, if multiple JSON objects are technically equivalent, but each JSON object contains the members in a different order, the JSON_TABLE() function can return different output for each JSON object. When an ordinality column is specified, the order of members can also affect the output.

When the JSON_TABLE() function is used to write to a table on a primary server using statement-based replication (SBR), the replica server will have to locally re-execute the statement containing the JSON_TABLE() function. If the function returns different output, the replica server can become inconsistent with the primary server.

To safely use the JSON_TABLE() function with replication, set the binlog_format system variable to ROW or MIXED.

RESPONSES

DIAGNOSIS

ISO 9075:2016

The ISO 9075:2016 standard defines the JSON_TABLE() function. Features related to JSON_TABLE() include:

Feature ID	Feature Name
T821	Basic SQL/JSON query operators
T825	SQL/JSON: ON EMPTY and ON ERROR clauses
T826	General value expression in ON ERROR or ON EMPTY clauses
T827	JSON_TABLE: sibling NESTED COLUMNS clauses

The following JSON table column definition are supported:

JSON table ordinality column definition
JSON table regular column definition
JSON table nested columns

The following features are not defined in ISO 9075:

Clause EXISTS PATH

For additional information on the standard, see ISO/IEC 9075-2:2016, 4.46 JSON data handling in SQL and ISO/IEC 19075-6:2017 Technical Report on SQL Support for JavaScript Object Notation (JSON)

CHANGE HISTORY

Release Series	History
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Added in MariaDB Community Server 10.6.0.
10.5 Enterprise	Not present.
10.5 Community	Not present.
10.4 Enterprise	Not present.
10.4 Community	Not present.
10.3 Enterprise	Not present.
10.3 Community	Not present.
10.2 Enterprise	Not present.
10.2 Community	Not present.

Release Series	History
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Not present.
10.4 Enterprise	Not present.