JSON_TABLE()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Enterprise Server
Topics on this page:
Overview
In 11.4 ES:
A description for this Function has not yet been added to this Documentation.
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 inSELECT
statements by calling it in theFROM
orJOIN
clauses. It can also be used in a nestedSELECT
within another statement, such asCREATE TABLE .. AS SELECT
,INSERT INTO .. SELECT
, andDELETE FROM ... WHERE ... SELECT
.JSON_TABLE()
can be used in the multi-table syntax of theDELETE
andUPDATE
statements.JSON_TABLE()
requires a table alias.JSON_TABLE()
uses theutf8mb4
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 setsNULL ON EMPTY
andNULL ON ERROR
for columns, which means that the column's value is set toNULL
if its value cannot be found or if an error occurs while parsing its value.
PARAMETERS
Parameter | Description |
---|---|
| 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. |
| 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 |
---|---|
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. | |
Specifies an ordinality column. Ordinality columns provide sequential numbering of rows starting at 1 | |
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. | |
Tests the JSON Path expression provided. It returns | |
Specifies nested columns defined by the specified nested JSON Path. Nested JSON Paths can be nested to multi-levels. | |
Specifies behavior if the result of a JSON Path expression is empty. | |
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 |
| Matches the first item in the root JSON Array or auto-promoted single-item array
|
| Matches every member of the root JSON Object |
| Matches the member of the root JSON Object with key |
| The member of the root JSON Object with key
|
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 |
name type PATH path_ | 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_ | Creates a regular column that has a value of either |
NESTED PATH path COLUMNS (columns_ | 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 |
---|---|
Sets the column to | |
Sets the column to the specified value expression when the column's value is missing from the JSON data. | |
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 |
---|---|
Sets the column to | |
Sets the column to the specified value expression when an error occurs. | |
Throws an error when an error occurs. |
EXAMPLES
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 theCOLUMNS()
clauses at different levelsSibling nested paths use multiple
NESTED PATH
clauses in the sameCOLUMNS()
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 rootCOLUMNS()
clause, so they are examples of sibling nested paths. With sibling nested paths, theNESTED PATH
clauses are processed sequentially and are returned as separate rows. The output is processed similarly to anOUTER JOIN
. When one nested path is being processed, the columns of any sibling nested path expressions are set toNULL
.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 isrowid
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 arerowid_email
androwid_family
in the above example, are useful for showing which elements represent siblings. When two rows have the samerowid
value, but one row setsrowid_email
and the other row setsrowid_family
, they represent two sibling elements.The ordinality columns defined in the
COLUMNS()
clauses for hierarchical nested paths, which arerowid_family
androwid_email_family
in the above example, are useful for showing which elements correspond in the hierarchy. When two rows have the samerowid_family
value, but they have differentrowid_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_
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_
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_
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_
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_
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_
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_ANSI_QUOTES
, double quotation marks ("
) are only allowed to quite identifiers. Therefore, when sql_ANSI_QUOTES
, double quotation marks ("
) cannot be used to quote JSON literals or JSON Paths.
It should be noted that several sql_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_ROW
or MIXED
.
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_ |
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)