In MySQL, JSON_TABLE() is a built-in function that converts JSON data into a relational form.
In other words, it lets you return a JSON document as a table.
The JSON_TABLE() function was introduced in MySQL 8.0.
Syntax
The syntax goes like this:
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
Where column_list goes like this:
column[, column][, ...]Where column goes like this:
name FOR ORDINALITY
| name type PATH string path [on_empty] [on_error]
| name type EXISTS PATH string path
| NESTED [PATH] path COLUMNS (column_list)Where on_empty goes like this:
{NULL | DEFAULT json_string | ERROR} ON EMPTYAnd on_error goes like this:
{NULL | DEFAULT json_string | ERROR} ON ERRORExample
Here’s an example to demonstrate:
SET @json_document = '
[
{ "name": "Wag", "type": "Dog", "weight": 20 },
{ "name": "Bark", "type": "Dog", "weight": 10 },
{ "name": "Meow", "type": "Cat", "weight": 7 }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
name VARCHAR(255) PATH '$.name',
type VARCHAR(50) PATH '$.type',
weight INT PATH '$.weight'
)
) AS jt;Result:
+------+------+--------+ | name | type | weight | +------+------+--------+ | Wag | Dog | 20 | | Bark | Dog | 10 | | Meow | Cat | 7 | +------+------+--------+
Here, we name each column for the table, specify its data type, and then specify the path from the JSON document that will apply to that column.
So we called our first column name, and then mapped the node called name from the JSON document to that column.
Ordinality Columns
The FOR ORDINALITY option can be used to count the rows, starting from 1.
SET @json_document = '
[
{ "name": "Scratch", "type": "Cat", "weight": 8 },
{ "name": "Bruce", "type": "Kangaroo", "weight": 100 },
{ "name": "Hop", "type": "Kangaroo", "weight": 130 }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
id FOR ORDINALITY,
name VARCHAR(255) PATH '$.name',
type VARCHAR(50) PATH '$.type',
weight INT PATH '$.weight'
)
) AS jt;Result:
+------+---------+----------+--------+ | id | name | type | weight | +------+---------+----------+--------+ | 1 | Scratch | Cat | 8 | | 2 | Bruce | Kangaroo | 100 | | 3 | Hop | Kangaroo | 130 | +------+---------+----------+--------+
Checking the Existence of a Path
You can use the EXISTS clause to check for the existence of a path. If the path exists in the JSON document, the result is 1. If it doesn’t exist, 0 is returned.
SET @json_document = '
[
{ "name": "Punch", "type": "Kangaroo", "weight": 200 },
{ "name": "Snap", "type": "Cat", "weight": 12 },
{ "name": "Ruff", "type": "Dog" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
name VARCHAR(255) PATH '$.name',
type VARCHAR(50) PATH '$.type',
has_weight INT EXISTS PATH '$.weight'
)
) AS jt;Result:
+-------+----------+------------+ | name | type | has_weight | +-------+----------+------------+ | Punch | Kangaroo | 1 | | Snap | Cat | 1 | | Ruff | Dog | 0 | +-------+----------+------------+
Nested Paths
The NESTED PATH clause allows you to deal with nested JSON documents. When you use this clause, it converts nested JSON structures into multiple rows.
Example:
SET @json_document = '
[
{ "product": "Left Handed Screwdriver", "sizes": [ "S", "M", "L" ] },
{ "product": "Long Weight", "sizes": [ "S", "L", "XL" ] },
{ "product": "Bottomless Coffee Cup" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
product VARCHAR(255) PATH '$.product',
NESTED PATH '$.sizes[*]' columns (
size VARCHAR(2) PATH '$'
)
)
) AS jt;Result:
+-------------------------+------+ | product | size | +-------------------------+------+ | Left Handed Screwdriver | S | | Left Handed Screwdriver | M | | Left Handed Screwdriver | L | | Long Weight | S | | Long Weight | L | | Long Weight | XL | | Bottomless Coffee Cup | NULL | +-------------------------+------+
Dealing with Empty Paths
The ON EMPTY clause specifies what will be done when the element specified by the search path is missing in the JSON document.
Example:
SET @json_document = '
[
{ "name": "Punch", "type": "Kangaroo", "weight": 200 },
{ "name": "Snap", "type": "Cat", "weight": 12 },
{ "name": "Ruff" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
name VARCHAR(255) PATH '$.name',
type VARCHAR(50) PATH '$.type' DEFAULT '"N/A"' ON EMPTY,
weight INT PATH '$.weight'
)
) AS jt;Result:
+-------+----------+--------+ | name | type | weight | +-------+----------+--------+ | Punch | Kangaroo | 200 | | Snap | Cat | 12 | | Ruff | N/A | NULL | +-------+----------+--------+
In this example, Ruff doesn’t have a type field and therefore N/A is returned. This is because I specified that in the ON EMPTY clause for that field.
Dealing with Errors
The ON ERROR clause specifies what should be done if a JSON structure error occurs when trying to extract the value from the document.
A JSON structure error occurs only when you attempt to convert a JSON non-scalar (array or object) into a scalar value. When the ON ERROR clause is not present, NULL ON ERROR is implied.
Here’s an example of handling a JSON structure error:
SET @json_document = '
[
{ "product": "Left Handed Screwdriver", "sizes": [ "S", "M", "L" ] },
{ "product": "Long Weight", "sizes": [ "S", "L", "XL" ] },
{ "product": "Bottomless Coffee Cup" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
product VARCHAR(255) PATH '$.product',
sizes VARCHAR(5) PATH '$.sizes'
DEFAULT '"Oops!"' ON ERROR
DEFAULT '"None"' ON EMPTY
)
) AS jt;Result:
+-------------------------+-------+ | product | sizes | +-------------------------+-------+ | Left Handed Screwdriver | Oops! | | Long Weight | Oops! | | Bottomless Coffee Cup | None | +-------------------------+-------+
Here, I specified a string (Oops!) to use whenever a JSON structure error occurred.
In this example I also included the ON EMPTY clause. This demonstrates that both the ON ERROR and the ON EMPTY clause can be used in the same statement.
Here’s what happens when we remove ON ERROR and the ON EMPTY clauses from the above statement:
SET @json_document = '
[
{ "product": "Left Handed Screwdriver", "sizes": [ "S", "M", "L" ] },
{ "product": "Long Weight", "sizes": [ "S", "L", "XL" ] },
{ "product": "Bottomless Coffee Cup" }
]
';
SELECT * FROM JSON_TABLE(@json_document, '$[*]'
COLUMNS (
product VARCHAR(255) PATH '$.product',
sizes VARCHAR(5) PATH '$.sizes'
)
) AS jt;Result:
+-------------------------+-------+ | product | sizes | +-------------------------+-------+ | Left Handed Screwdriver | NULL | | Long Weight | NULL | | Bottomless Coffee Cup | NULL | +-------------------------+-------+
We get NULL for those rows, because that’s the default behaviour.