MySQL JSON_TABLE: Convert JSON Arrays into Rows

JSON documents often hold lists: the items of an order, the tags of a post. To filter, join or aggregate them with SQL, you want them as rows. JSON_TABLE does exactly that. It is available from MySQL 8.0.4.

query.sql

SQL

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer VARCHAR(20) NOT NULL,
    doc JSON NOT NULL
);
INSERT INTO orders VALUES
    (1, 'Maya',  '{"items": [{"sku": "NB-01", "qty": 2, "price": 80}, {"sku": "GP-02", "qty": 10, "price": 15.5}], "tags": ["gift", "express"]}'),
    (2, 'Rohan', '{"items": [{"sku": "BP-03", "qty": 1, "price": 999}], "tags": ["standard"]}');

The syntax

Text

PLAIN

JSON_TABLE(json_doc, path COLUMNS (
    column_name type PATH 'path' [ON EMPTY ...] [ON ERROR ...],
    ...
)) AS alias

The path after the document selects the array whose elements become rows ($.items[*]). Each column’s own path is relative to one element.

query.sql

SQL

SELECT o.id AS order_id, o.customer, it.sku, it.qty, it.price, it.qty * it.price AS line_total
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (
         sku   VARCHAR(10)   PATH '$.sku',
         qty   INT           PATH '$.qty',
         price DECIMAL(8,2)  PATH '$.price'
     )) AS it
ORDER BY o.id, it.sku;

Output

OUTPUT

+----------+----------+-------+------+--------+------------+
| order_id | customer | sku   | qty  | price  | line_total |
+----------+----------+-------+------+--------+------------+
|        1 | Maya     | GP-02 |   10 |  15.50 |     155.00 |
|        1 | Maya     | NB-01 |    2 |  80.00 |     160.00 |
|        2 | Rohan    | BP-03 |    1 | 999.00 |     999.00 |
+----------+----------+-------+------+--------+------------+
3 rows in set

Each element of items became a row, and the typed columns make qty * price work with no casts. The comma between orders o and JSON_TABLE(...) is an implicit lateral join: for each order, the function expands that order’s document (MySQL allows referring to an earlier table in JSON_TABLE).

Totals per order

query.sql

SQL

SELECT o.id, o.customer, SUM(it.qty * it.price) AS order_total, COUNT(*) AS line_count
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (qty INT PATH '$.qty', price DECIMAL(8,2) PATH '$.price')) AS it
GROUP BY o.id, o.customer
ORDER BY o.id;

Output

OUTPUT

+----+----------+-------------+------------+
| id | customer | order_total | line_count |
+----+----------+-------------+------------+
|  1 | Maya     |      315.00 |          2 |
|  2 | Rohan    |      999.00 |          1 |
+----+----------+-------------+------------+
2 rows in set

ORDINALITY: the position in the array

query.sql

SQL

SELECT o.id, it.position, it.sku
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (position FOR ORDINALITY, sku VARCHAR(10) PATH '$.sku')) AS it
ORDER BY o.id, it.position;

Output

OUTPUT

+----+----------+-------+
| id | position | sku   |
+----+----------+-------+
|  1 |        1 | NB-01 |
|  1 |        2 | GP-02 |
|  2 |        1 | BP-03 |
+----+----------+-------+
3 rows in set

FOR ORDINALITY numbers the rows 1, 2, 3 in array order.

An array of plain values

For ["gift", "express"], use the path $[*] for the column (each element is the value itself):

query.sql

SQL

SELECT o.id, t.tag
FROM orders o,
     JSON_TABLE(o.doc, '$.tags[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS t
ORDER BY o.id, t.tag;

Output

OUTPUT

+----+----------+
| id | tag      |
+----+----------+
|  1 | express  |
|  1 | gift     |
|  2 | standard |
+----+----------+
3 rows in set

This is also the tool for splitting a list stored in JSON into rows, instead of the string tricks in Splitting Text.

query.sql

SQL

SELECT j.value AS item FROM JSON_TABLE(CAST(CONCAT('["', REPLACE('red,green,blue', ',', '","'), '"]') AS JSON), '$[*]' COLUMNS (value VARCHAR(10) PATH '$')) AS j;

Output

OUTPUT

+-------+
| item  |
+-------+
| red   |
| green |
| blue  |
+-------+
3 rows in set

Wrapping a comma-separated string into a JSON array and then using JSON_TABLE is a neat way to split a delimited string.

Missing and wrong values: ON EMPTY and ON ERROR

query.sql

SQL

SELECT it.sku, it.discount, it.qty_text
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (
         sku       VARCHAR(10)  PATH '$.sku',
         discount  INT          PATH '$.discount' DEFAULT '0' ON EMPTY,
         qty_text  INT          PATH '$.sku' NULL ON ERROR
     )) AS it
WHERE o.id = 1
ORDER BY it.sku;

Output

OUTPUT

+-------+----------+----------+
| sku   | discount | qty_text |
+-------+----------+----------+
| GP-02 |        0 |     NULL |
| NB-01 |        0 |     NULL |
+-------+----------+----------+
2 rows in set

DEFAULT '0' ON EMPTY supplies a value when the key is missing. NULL ON ERROR returns NULL when the value cannot be converted (here the SKU text is not an integer). The options are NULL, DEFAULT value, or ERROR.

EXISTS and nested arrays

EXISTS PATH returns 1 or 0 for whether a path exists. NESTED PATH expands an array inside each element, giving one row per inner element:

query.sql

SQL

CREATE TABLE posts (id INT PRIMARY KEY, doc JSON NOT NULL);
INSERT INTO posts VALUES (1, '{"title": "Intro", "comments": [{"by": "ben", "likes": ["asha", "dev"]}, {"by": "chitra", "likes": []}]}');

SELECT c.author, c.liked_by
FROM posts p,
     JSON_TABLE(p.doc, '$.comments[*]' COLUMNS (
         author VARCHAR(10) PATH '$.by',
         NESTED PATH '$.likes[*]' COLUMNS (liked_by VARCHAR(10) PATH '$')
     )) AS c
ORDER BY c.author, c.liked_by;

Output

OUTPUT

+--------+----------+
| author | liked_by |
+--------+----------+
| ben    | asha     |
| ben    | dev      |
| chitra | NULL     |
+--------+----------+
3 rows in set

Chitra’s empty likes array still produces a row, with NULL for liked_by (like a left join). Ben has two rows.

Join the result to real tables

The rows from JSON_TABLE can be joined like any other:

query.sql

SQL

CREATE TABLE products (sku VARCHAR(10) PRIMARY KEY, name VARCHAR(30), category VARCHAR(20));
INSERT INTO products VALUES ('NB-01', 'Notebook', 'stationery'), ('GP-02', 'Gel Pen', 'stationery'), ('BP-03', 'Backpack', 'bags');

SELECT p.category, SUM(it.qty) AS units
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (sku VARCHAR(10) PATH '$.sku', qty INT PATH '$.qty')) AS it
JOIN products p ON p.sku = it.sku
GROUP BY p.category
ORDER BY units DESC;

Output

OUTPUT

+------------+-------+
| category   | units |
+------------+-------+
| stationery |    12 |
| bags       |     1 |
+------------+-------+
2 rows in set

Performance and design

JSON_TABLE has to parse and expand each document. It is fine for a few thousand documents, and slow when you expand millions in every query.
You cannot index the output of JSON_TABLE. If you often filter or join on the items, they should be rows in a real table (order_items).
It is a great tool for importing JSON (an API response) into normal tables: INSERT INTO order_items SELECT ... FROM orders, JSON_TABLE(...), and for reports that read JSON once.

query.sql

SQL

CREATE TABLE order_items (order_id INT, sku VARCHAR(10), qty INT, price DECIMAL(8,2), PRIMARY KEY (order_id, sku));
INSERT INTO order_items (order_id, sku, qty, price)
SELECT o.id, it.sku, it.qty, it.price
FROM orders o, JSON_TABLE(o.doc, '$.items[*]' COLUMNS (sku VARCHAR(10) PATH '$.sku', qty INT PATH '$.qty', price DECIMAL(8,2) PATH '$.price')) AS it;
SELECT COUNT(*) AS items_imported FROM order_items;

Output

OUTPUT

+----------------+
| items_imported |
+----------------+
|              3 |
+----------------+
1 row in set

The relational result is normalised data you can index, constrain and join.

lightbulb

Typed columns do the casting

Declare each column with the SQL type you need (DECIMAL(8,2), DATE, INT). JSON_TABLE converts for you, and ON ERROR decides what happens to bad values.