When my team first switched to storing configs and event logs as JSON in MySQL, everything seemed clean at first. But when reporting time came — pulling out individual fields, aggregating, JOINing with other tables — the pain set in. Nested JSON_EXTRACT() calls were becoming a maintenance nightmare. That’s when I stumbled upon JSON_TABLE(), a function available since MySQL 8.0 but rarely covered in typical tutorials.
Three Approaches to Querying Nested JSON
Suppose you have an orders table with an items column storing a JSON array:
[
{"product_id": 101, "name": "Laptop", "qty": 1, "price": 25000000},
{"product_id": 205, "name": "Mouse", "qty": 2, "price": 350000}
]When you need a report on “how many units of each product were sold,” you have three practical options:
Option 1: JSON_EXTRACT() / JSON_VALUE()
SELECT
id,
JSON_EXTRACT(items, '$[0].name') AS first_item,
JSON_EXTRACT(items, '$[0].qty') AS first_qty
FROM orders;Simple, but the problem is you have to hard-code the index ($[0], $[1]…). Each order has a different number of items? You’d have to rewrite the query accordingly. And GROUP BY across all items is impossible.
Option 2: Application-layer processing
Pull all the JSON into Python or PHP, loop through each element, and aggregate in code. This approach is flexible but burns memory and CPU on the app server. I once saw a PHP service crash with an OOM error from pulling 500k JSON rows to process in-app — not fun, especially at midnight.
Option 3: JSON_TABLE() — converting directly into a relational table
SELECT
o.id AS order_id,
t.product_id,
t.name,
t.qty,
t.price
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t;JSON_TABLE() flattens JSON arrays into rows, and from there you use standard SQL: GROUP BY, JOIN, WHERE, aggregate functions — all working just like with a real table.
Comparing the Pros and Cons of Each Approach
JSON_EXTRACT() / JSON_VALUE() — Best when you only need to pull 1-2 individual fields from a JSON object (not an array with multiple elements). Short syntax, simple query plan. Use it in the wrong scenario and it becomes a mess.
Application-layer processing — Flexible. But poor in terms of network bandwidth and memory — pulling a million JSON rows to the app for aggregation will hit a request timeout before results can be returned. Only use this when the logic is too complex to express in SQL.
JSON_TABLE() — Most powerful for flattening arrays and complex queries. Processing stays entirely in the database — MySQL handles the execution plan. Downsides: longer syntax, requires MySQL 8.0+, and indexes don’t apply directly to the virtual table.
When to Use JSON_TABLE()
JSON_TABLE() isn’t a tool for every JSON problem — but in these 4 situations, not having it is painful:
- A JSON column contains an array and you need to unpack each element to GROUP BY or calculate totals
- You need to JOIN JSON results with another relational table (e.g., JOIN
productsbyproduct_idfrom JSON) - Periodic reporting on JSON data — write the query once, run it many times
- Data migration: read legacy JSON, transform, and insert into a normalized schema
On the other hand, if you just need to filter by a single field in a flat JSON object, JSON_VALUE() is simpler and does the job.
Practical JSON_TABLE() Implementation Guide
Setting Up Test Data
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
items JSON
);
INSERT INTO orders (customer_id, order_date, items) VALUES
(1, '2024-01-15', '[{"product_id":101,"name":"Laptop","qty":1,"price":25000000},{"product_id":205,"name":"Mouse","qty":2,"price":350000}]'),
(2, '2024-01-16', '[{"product_id":101,"name":"Laptop","qty":2,"price":25000000},{"product_id":301,"name":"Keyboard","qty":1,"price":800000}]'),
(1, '2024-01-17', '[{"product_id":205,"name":"Mouse","qty":1,"price":350000}]');Total Revenue Report by Product
SELECT
t.product_id,
t.name,
SUM(t.qty) AS total_qty,
SUM(t.qty * t.price) AS total_revenue
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t
GROUP BY t.product_id, t.name
ORDER BY total_revenue DESC;The results come out exactly as they would from a real relational table — no additional processing needed at the application layer.
Handling Multi-Level Nested JSON with NESTED PATH
When JSON has multiple nesting levels — for example, a parent object containing a child array — use NESTED PATH:
-- JSON structure: {"order_id": 1, "customer": {"name": "Nguyen Van A"}, "items": [...]}
SELECT
t.order_id,
t.customer_name,
t.product_name,
t.qty
FROM raw_import_data,
JSON_TABLE(
payload, '$'
COLUMNS (
order_id INT PATH '$.order_id',
customer_name VARCHAR(100) PATH '$.customer.name',
NESTED PATH '$.items[*]' COLUMNS (
product_name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
)
) AS t;NESTED PATH preserves the parent row’s context while unpacking each item from the child array — something plain JSON_EXTRACT() simply can’t do.
Using FOR ORDINALITY to Track Position in an Array
SELECT
o.id,
t.row_num, -- Position in the original array (1-based)
t.name,
t.qty
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
row_num FOR ORDINALITY,
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
) AS t;Handling Inconsistent Data with ON ERROR / ON EMPTY
JSON data from production sometimes has messy formatting — missing fields or wrong data types. Add ON EMPTY and ON ERROR to prevent query crashes:
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id' DEFAULT '0' ON EMPTY DEFAULT '-1' ON ERROR,
name VARCHAR(100) PATH '$.name' DEFAULT 'Unknown' ON EMPTY,
price DECIMAL(15,2) PATH '$.price' DEFAULT '0.00' ON EMPTY
)
)Instead of the entire query failing because of one bad JSON row, you get default values — far more production-safe.
Production Considerations
Indexes don’t apply directly — JSON_TABLE() generates virtual rows, so MySQL can’t use indexes on the JSON column to speed things up. If queries are slow, consider creating a generated column with an index for fields frequently used in WHERE clauses.
Always EXPLAIN before deploying — Run EXPLAIN FORMAT=JSON SELECT ... to review the execution plan. A 100k-row table with a JSON_TABLE query and no good filter — expect a full table scan, taking seconds instead of milliseconds.
Validate JSON before querying — Invalid JSON in a column will cause JSON_TABLE() to throw an error. Pre-filter with:
SELECT ...
FROM orders o, JSON_TABLE(...) AS t
WHERE JSON_VALID(o.items) = 1;Back up before migration — I once dealt with database corruption at 3 a.m. that required a restore from backup, costing over two hours. Since then, I always run mysqldump before any migration script. If you’re using JSON_TABLE() to migrate from a JSON column to a normalized schema, dump that table first.
Before writing path expressions for JSON_TABLE(), it’s worth validating your JSON structure with the JSON Formatter on ToolCraft — format and validate instantly in the browser. The tool runs entirely client-side, so there’s no risk of your production data being sent anywhere.
Conclusion
JSON_TABLE() solves a specific problem: flattening JSON arrays for complex queries. JSON_EXTRACT() can’t do this; application-side processing wastes bandwidth and memory. The syntax looks long at first glance, but a few queries in and it becomes second nature. If you’re currently looping through JSON at the app layer just to GROUP BY or JOIN — try pushing it down to the database. The queries end up cleaner and typically run significantly faster.
