MySQL JSON_TABLE: Shredding JSON Columns into Relational Rows

Home » MySQL » MySQL JSON_TABLE: Shredding JSON Columns into Relational Rows
September 15, 2025 MySQL
MySQL JSON_TABLE: Shredding JSON Columns into Relational Rows

MySQL 8.0.4+ ships the JSON_TABLE() function — a table-valued function that converts a JSON document (or array) stored in a column into a virtual relational table you can join, filter, and aggregate just like a regular table. This is invaluable when WordPress post meta or WooCommerce order meta stores arrays or objects as JSON and you need to query individual fields without PHP-side loops.

Problem: WooCommerce product metadata is stored as JSON in a text column — querying products by a specific attribute inside the JSON requires a LIKE scan across the entire column, which is slow and cannot use indexes.

Solution: Use JSON_TABLE() to shred the JSON column into relational rows inline in a query. Combine with a VIRTUAL GENERATED column to extract a specific JSON key into an indexable column. For reporting queries, use JSON_TABLE() in a CTE to join JSON data with relational tables without materialising intermediate tables.


The queries below extract WooCommerce order items stored as a JSON array in post meta, flatten a nested JSON object into rows with JSON_TABLE, and combine the result with a regular table join and GROUP BY.


-- Assume wp_postmeta stores order data as JSON in meta_key '_order_json'
-- Example JSON value:
-- {"items":[{"id":5,"name":"T-Shirt","qty":2,"price":29.99},
--           {"id":8,"name":"Mug","qty":1,"price":14.99}]}

-- 1. Shred the JSON items array into rows with JSON_TABLE
SELECT
    pm.post_id                  AS order_id,
    jt.item_id,
    jt.item_name,
    jt.qty,
    jt.price,
    jt.qty * jt.price           AS line_total
FROM wp_postmeta pm
JOIN JSON_TABLE(
    pm.meta_value,
    '$.items[*]'               -- path to iterate over (the array)
    COLUMNS (
        item_id   INT          PATH '$.id',
        item_name VARCHAR(255) PATH '$.name',
        qty       INT          PATH '$.qty',
        price     DECIMAL(10,2) PATH '$.price'
    )
) AS jt
WHERE pm.meta_key = '_order_json'
  AND pm.post_id  IN (SELECT ID FROM wp_posts WHERE post_status = 'publish' AND post_type = 'shop_order');

-- 2. Aggregate: total revenue per product across all orders
SELECT
    jt.item_id,
    jt.item_name,
    SUM( jt.qty )               AS total_qty_sold,
    SUM( jt.qty * jt.price )    AS total_revenue
FROM wp_postmeta pm
JOIN JSON_TABLE(
    pm.meta_value, '$.items[*]'
    COLUMNS (
        item_id   INT          PATH '$.id',
        item_name VARCHAR(255) PATH '$.name',
        qty       INT          PATH '$.qty',
        price     DECIMAL(10,2) PATH '$.price'
    )
) AS jt
WHERE pm.meta_key = '_order_json'
GROUP BY jt.item_id, jt.item_name
ORDER BY total_revenue DESC
LIMIT 10;

-- 3. Nested JSON object (not array) — shred object fields into a single row
SELECT
    pm.post_id,
    jt.first_name,
    jt.last_name,
    jt.city
FROM wp_postmeta pm
JOIN JSON_TABLE(
    pm.meta_value,
    '$'                        -- root object (not an array)
    COLUMNS (
        first_name VARCHAR(100) PATH '$.billing.first_name',
        last_name  VARCHAR(100) PATH '$.billing.last_name',
        city       VARCHAR(100) PATH '$.billing.city'
    )
) AS jt
WHERE pm.meta_key = '_billing_json';


NOTE: JSON_TABLE() has no index support — it scans and parses the JSON column on every row it processes; for frequently-executed queries, extract the needed JSON fields into generated columns with a regular index, or store the data in a normalised table from the start.

Related posts