Using JSON_TABLE to Turn MySQL JSON Into Rows and Columns

Before JSON_TABLE, getting rows out of a MySQL JSON array meant a numbers table, a json_extract with a built path and a lot of squinting. MySQL 8.0.4 added the SQL-standard function that does it properly: you describe the columns once, with types and paths, and the document becomes a table you can join, filter and aggregate. This article covers the syntax with outputs from MySQL 8.4, nested arrays, the clauses that handle missing and bad values, and the places where it fails without telling you.

The shape of a call

JSON_TABLE sits in the FROM clause. Its first argument is the document, the second a path that selects the rows, then a COLUMNS list:

select o.id, jt.*
from orders o,
     json_table(o.doc, '$.items[*]' columns (
       line   for ordinality,
       sku    varchar(10)   path '$.sku',
       qty    int           path '$.qty',
       price  decimal(8,2)  path '$.price',
       coupon varchar(10)   path '$.coupon' default '"none"' on empty
     )) jt
order by o.id, jt.line;

id  line  sku  qty  price  coupon
1   1     A1   2    9.50   none
1   2     B2   1    20.00  none
2   1     A1   5    9.50   none

The comma before json_table is an implicit lateral join: the function can refer to columns of tables to its left, which is what lets each order's own document feed it. The row path $.items[*] yields one row per element, and each column path is evaluated relative to that element. The coupon key does not exist in any element; default '"none"' on empty supplied a value. Note the quoting: the default is a JSON literal written as a string, so a string value needs its own double quotes inside single quotes, '"none"'. For a number, default '99' on empty is enough.

Order 3 has an empty items array and produces no rows in an inner join. Use a left join to keep it: from orders o left join json_table(o.doc, '$.items[*]' columns (...)) jt on true returned order 3 with a NULL sku, and also the orders from my earlier experiments with no items.

Column types

Each column is one of four kinds:

• A typed column with a path, as above. Any MySQL type works for scalars: int, decimal, varchar, date, datetime. A string "2026-10-01" into a date column returned a date.

• **FOR ORDINALITY** numbers the rows 1, 2, 3 within each document.

• **EXISTS PATH** gives 1 or 0 for whether the path matches anything: e int exists path '$.a' was 1 and z int exists path '$.zz' was 0.

• **NESTED PATH** descends into a sub-array, covered next.

To keep a sub-document as JSON, give the column the type json: a json path '$.a' on {"a":{"b":1}} returned {"b": 1}.

Nested arrays

An order has lines, lines have options, and NESTED PATH lets one call produce one row per innermost element while repeating the outer values:

select jt.*
from orders o,
     json_table(o.doc, '$' columns (
       cust_id int path '$.customer.id',
       nested path '$.items[*]' columns (sku varchar(10) path '$.sku', qty int path '$.qty'),
       has_note int exists path '$.note'
     )) jt
order by 1, 2;

cust_id  sku   qty   has_note
10       NULL  NULL  1
10       A1    2     0
10       B2    1     0
11       A1    5     0

The result keeps the order with no items (customer 10, the one with a note) as a row with NULLs, so NESTED PATH behaves like a left join. The $ row path made one row per document; the nested path fanned it out. Several levels nest the same way, with nested path inside nested path.

Siblings do not multiply. Two NESTED PATH clauses at the same level look like they should produce a cross product, and they do not. On a document with two emails and two orders, the output was:

user_id  email             order_id
7        NULL              100
7        NULL              101
7        [email protected]    NULL
7        [email protected]  NULL
8        NULL              NULL

Each branch contributes its own rows, padded with NULLs, which is a union of the two lists and not their product. That is what you want for two unrelated lists, and it surprises people who expect every email beside every order. If you want the product, use two JSON_TABLE calls and join them. PostgreSQL's JSON_TABLE behaves the same way, which the PostgreSQL version of this article confirms in using JSON_TABLE in PostgreSQL 17.

Empty and error handling

Each column can say what to do when the path finds nothing (ON EMPTY) and when the value cannot be converted (ON ERROR). The order of the two clauses is fixed: empty first, then error.

a int path '$.a' default '99' on empty default '98' on error

On the input [{"a":"x"}] that column returned 98: the key exists, so it is not empty, but "x" is not an integer, so the error clause applied. With [{"a":1},{"b":2}], a int path '$.a' default '99' on empty returned 1 and 99. The options are NULL, DEFAULT 'json literal' and ERROR, and the default for both is NULL. A strict version, error on empty error on error, stopped on the first problem with Invalid JSON value for CAST to INTEGER from column a at row 1.

The silent NULLs

The defaults are quiet, and some failures that look like errors are NULLs:

• An array or object into a scalar column returned NULL: a int path '$.a' over {"a":[1,2]} gave NULL and no warning.

• An **object into a text column** returned NULL, not the JSON text. Use a json column and cast later.

• A **string longer than a varchar(n)** column returned NULL: name varchar(2) path '$.name' over "Ada" gave NULL, not Ad. The truncation is not silent in the usual MySQL sense, where a plain insert errors in strict mode or warns; here the whole value is dropped.

• A missing key is NULL.

So a NULL in the output has at least four causes and the function does not say which. For a load that matters, choose error on error and error on empty for the columns that must be present, and compare row counts and count(col) to what you expect. Combined with a left join, a NULL that comes from "no row at all" is also indistinguishable from "row with null value", so add an exists path column or a for ordinality column to tell them apart.

Methods and filters are not available

PostgreSQL's path language includes filters and methods such as $.a.size() and ?(@.qty > 3). MySQL's does not. a int path '$.a.length()' failed with Invalid JSON path expression. The error is around character position 12. Filter in SQL instead, with a where on the JSON_TABLE output, and count elements with json_length outside. This is a real gap when porting queries from PostgreSQL.

Speed

The test: sum qty * price over all items in big_orders, 250,000 items in 50,000 documents.

select sum(jt.qty * jt.price)
from big_orders o,
     json_table(o.doc, '$.items[*]' columns (qty int path '$.qty', price decimal(8,2) path '$.price')) jt;

It returned 33197290.00 in 131 ms (the explain analyze showed a nested loop with Materialize table function returning 5 rows per document). The old approach, a cross join with a derived table of index numbers 0 to 4 and json_extract(o.doc, concat('$.items[', n.i, '].qty')), took 319 ms and returned 33197289.999998327, because the extracted numbers are doubles. JSON_TABLE was 2.4 times faster and exact, since the columns were declared decimal. Once more the lesson is that typing the columns is not only convenience: it decides the arithmetic.

Because the function materializes a temporary table per source row, it is not a free abstraction. Calling it on a few hundred thousand documents is fine; calling it in a correlated subquery for each row of a large table is a different matter, and explain analyze will show it.

Patterns

Flatten, then aggregate. select jt.sku, sum(jt.qty) from orders o, json_table(...) jt group by jt.sku is the standard report over embedded lines.

Join to a real table. The output is a normal row source: join products p on p.sku = jt.sku.

Load into a table. insert into order_items (order_id, sku, qty) select o.id, jt.sku, jt.qty from orders o, json_table(...) jt turns an embedded array into rows, the first step of a migration out of JSON (when JSONB is the wrong choice describes the same plan for PostgreSQL).

Bulk-import a JSON file. Load the whole file into a variable or a JSON column and JSON_TABLE it, for modest sizes: json_table('[1,2,3]', '$[*]' columns (n int path '$')) returned three rows. For large files, a streaming importer or DuckDB (querying JSON files with DuckDB) is the better tool.

Index the source, not the output. Rows from JSON_TABLE cannot be indexed. If the same unpacking runs constantly, store the rows.

For a first look at a document, the JSON formatter shows the shape you are about to describe in COLUMNS.

When JSON_TABLE is the wrong tool

You need one value. doc ->> '$.a' or json_value() is shorter than a one-column table function.

You want conditions on the elements. With no filters in the path, a JSON_TABLE plus a WHERE unpacks every element first. For "does any element match", JSON_CONTAINS, JSON_SEARCH or MEMBER OF with a multi-valued index avoids unpacking (searching values inside a MySQL JSON array).

The data is bad and you must know. The quiet NULLs make validation by JSON_TABLE unreliable. Validate first with JSON_SCHEMA_VALID (validating JSON before it gets into a MySQL column).

The same unpacking appears in many queries. Put it in a view or a real child table.

You must support MariaDB and MySQL with the same SQL. Both have the function, and details such as the exists column, the nested syntax and the error messages differ; test on each (MySQL JSON versus MariaDB JSON).

The syntax is in the manual: JSON_TABLE, including the NESTED PATH and ON EMPTY/ON ERROR rules above. The path syntax it relies on is described in the manual's JSON path syntax section.