Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 75 ยท JSON and Modern Features

JSON_TABLE, Multi-Valued Indexes and JSON Performance

Turn JSON arrays into rows with JSON_TABLE, search them with MEMBER OF and JSON_CONTAINS, and make JSON fast with multi-valued and generated-column indexes.

Storing a document is the easy part. The hard questions come later: which orders contain SKU A-100? Which products carry the tag eco? How many units of each item were sold, when the items live inside a JSON array in every order? Answering them with plain JSON_EXTRACT in a WHERE clause means scanning every row and parsing every document. MySQL 8 has three tools that fix this: JSON_TABLE expands arrays into rows, operators such as MEMBER OF ask "is this value in the array?", and multi-valued indexes and generated-column indexes make those questions fast.

This lesson builds on The JSON Data Type. Since JSON_TABLE, multi-valued indexes and the other 8.0 features need a MySQL server, most code is read-only here. SQLite's json_each is a close cousin of JSON_TABLE, and SQLite has indexes on expressions and generated columns, so the ideas can still be shown live.

Four tools for JSON at scale

JSON_TABLE Turn an array into rows and columns.
MEMBER OF Is this value inside the array?
Multi-valued index One index entry per array element.
Generated column Index one extracted key.

Without an index, every JSON filter is a full table scan.

1 / 2

JSON_TABLE: arrays become rows

JSON_TABLE is a table function. You give it a JSON document, a path that selects the repeating part, and a list of columns to pull out of each element. The result behaves like a table you can join, filter and aggregate. Suppose orders.items holds an array like [{"sku": "A-100", "qty": 2, "price": 12.5}, ...]:

SQL
SELECT o.id, o.customer, jt.line_no, jt.sku, jt.qty, jt.price
FROM orders AS o,
     JSON_TABLE(
       o.items,
       '$[*]' COLUMNS (
         line_no FOR ORDINALITY,
         sku     VARCHAR(20)   PATH '$.sku',
         qty     INT           PATH '$.qty'   DEFAULT '1' ON EMPTY,
         price   DECIMAL(10,2) PATH '$.price' ERROR ON ERROR
       )
     ) AS jt
ORDER BY o.id, jt.line_no;

Reading it from the top:

  • o.items is the document, taken from the row of orders that is currently being processed. A JSON_TABLE may refer to columns of tables to its left, so the comma join works as a lateral join: it runs once per order.
  • '$[*]' is the row path: every element of the top-level array becomes one row.
  • Each column has a name, a SQL type and a PATH relative to the element.
  • FOR ORDINALITY numbers the rows from 1, which preserves the position within the array.
  • DEFAULT ... ON EMPTY supplies a value when the path is missing. NULL ON ERROR (the default), DEFAULT ... ON ERROR or ERROR ON ERROR decide what happens when the value cannot be converted to the column type.
  • EXISTS PATH '$.gift' makes a column that is 1 if the path exists and 0 otherwise.
  • NESTED PATH '$.options[*]' COLUMNS (...) descends into an inner array and produces one row per inner element, repeating the outer columns.

The alias after JSON_TABLE(...) is required. An order with an empty array produces no rows in an inner join, so use LEFT JOIN JSON_TABLE(...) AS jt ON TRUE if you want to keep it. With the table function you can answer "units sold per SKU" with an ordinary GROUP BY jt.sku, and you can copy the result into a proper relational table with INSERT ... SELECT, which is a good way to migrate a JSON blob into columns.

SQLite's json_each produces one row per array element, with key (the zero-based position) and value columns. You pick fields out of value with ->>. It has no typed column list, so you cast yourself:

SQL json_each as a JSON_TABLE stand-in

Press Run (or Ctrl + Enter) to see the result here.

Ana's two lines have totals 25 and 40, Ben's one line is 62.5, and the second query reports 7 units of A-100 and 1 of B-200. The last query lists Cara with an empty sku, because the left join keeps her despite the empty array.

Searching arrays: MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS

Three MySQL operators test array membership without expanding anything. Imagine products.attrs is {"tags": ["eco", "kitchen", "steel"], ...}:

SQL
-- is one value in the array? (8.0.17+)
SELECT name FROM products WHERE 'eco' MEMBER OF (attrs->'$.tags');

-- does the array contain ALL of these values?
SELECT name FROM products
WHERE JSON_CONTAINS(attrs->'$.tags', '["eco", "steel"]');

-- does the array share ANY value with this list? (8.0.17+)
SELECT name FROM products
WHERE JSON_OVERLAPS(attrs->'$.tags', '["led", "steel"]');

JSON_CONTAINS(target, candidate) takes the candidate as a JSON document, so the string needs its square brackets and double quotes. Without a multi-valued index these functions work, but every row is parsed. In SQLite, the same three questions are written with json_each and a subquery:

SQL MEMBER OF, CONTAINS and OVERLAPS by hand

Press Run (or Ctrl + Enter) to see the result here.

The first query finds the bottle and the straw. The second finds only the bottle, the single product with both eco and steel. The third finds the bottle (it has steel) and the lamp (it has led). The tag counts show eco and kitchen on two products each. The last query builds a JSON array of names and an object of tag counts, as JSON_ARRAYAGG(name) and JSON_OBJECTAGG(name, ...) do in MySQL. Remember that these aggregates do not take an ORDER BY, so if the order matters, order the rows in a subquery first, and with JSON_OBJECTAGG make sure the keys are unique, because duplicate keys collapse into one.

Multi-valued indexes

A normal index holds one entry per row. An array has many values per row, so MySQL 8.0.17 added the multi-valued index: one index entry for each element of the array. You define it with a functional key part that casts the array path with the keyword ARRAY:

SQL
CREATE TABLE products (
  id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  attrs JSON NOT NULL,
  INDEX idx_tags ((CAST(attrs->'$.tags' AS CHAR(30) ARRAY)))
);

The optimizer then uses the index for MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS when the expression in the query matches the one in the index (attrs->'$.tags'). You can check with EXPLAIN: the key column should show idx_tags. Things to know:

  • The array must be cast to one scalar type: an integer type (UNSIGNED or SIGNED), a string (CHAR(n), BINARY(n)), or a date or time type.
  • String elements are compared by the binary rules of the index, so 'Eco' and 'eco' are different values.
  • An index can contain only one multi-valued key part, and only InnoDB supports them.
  • A row's array has a limit on how many values it can contribute, and every insert or update of the array maintains one entry per element. Tag lists of a handful of values are ideal. An array of ten thousand ids per row is not.
  • Inserting a value that cannot be cast to the declared type (a string that is too long for CHAR(30), for example) is an error in strict mode.

Indexing one key: generated columns and functional indexes

For a single scalar key inside a document, for example $.country, there are two ways to make filters fast. Both rest on the same idea: an index must be on something the engine can store, so you give the extracted value a name or an expression.

SQL
-- 1) a generated column with an ordinary index
ALTER TABLE users
  ADD COLUMN country CHAR(2) GENERATED ALWAYS AS (profile->>'$.country') VIRTUAL,
  ADD INDEX idx_users_country (country);

SELECT id FROM users WHERE country = 'PT';

-- 2) a functional index on an expression (8.0.13+)
CREATE INDEX idx_users_age ON users ((CAST(profile->>'$.age' AS UNSIGNED)));

SELECT id FROM users WHERE CAST(profile->>'$.age' AS UNSIGNED) > 30;

The generated column is the more flexible choice: you can select it, use it in any query, and the column has a real type and collation. A functional index is hidden, and the optimizer uses it only when the query repeats the same expression (same function, same cast, same path). A query that writes profile->>'$.age' > 30 without the cast will not use the index above. Two more traps: ->> returns text with a binary collation, so a plain comparison is case-sensitive, and indexing text without a cast would need a prefix length. Casting to a sized type, as above, avoids both. See Generated Columns for the VIRTUAL versus STORED choice.

SQLite supports both approaches too, and EXPLAIN QUERY PLAN shows the same lesson, including the miss when the expression differs:

SQL Generated column and expression index, and the plan

Press Run (or Ctrl + Enter) to see the result here.

The first two plans say SEARCH users USING INDEX ..., one on idx_users_country and one on idx_users_age. The third says SCAN users, a full table scan, because its expression differs from the indexed one. The final query returns users 1 and 3.

JSON performance checklist

  1. Promote hot keys to columns. Anything in a frequent WHERE, JOIN or ORDER BY should be a real or generated column with an index.
  2. Match expressions exactly. Keep the cast and path identical in the index and the query, and check with EXPLAIN.
  3. Keep documents small. Reading one key does not need the whole document in the engine, but a large document still has to be fetched, sent and stored, and it makes the row large. Move rarely used bulk into a separate table.
  4. Avoid LIKE on JSON text. Searching the text of a document cannot use an index and can match keys and punctuation by accident.
  5. Use JSON_TABLE for reporting, not for hot paths. Expanding arrays for millions of rows is a scan. If you aggregate over array elements all day, a child table (order_lines) is the better design.
  6. Prefer partial updates. JSON_SET, JSON_REPLACE and JSON_REMOVE can update in place, while assigning a whole new document always rewrites it.

What ran live here

json_each, json_group_array, json_group_object, generated columns and expression indexes ran in SQLite, with EXPLAIN QUERY PLAN as the closest relative of MySQL's EXPLAIN. JSON_TABLE, MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS and multi-valued indexes are MySQL server features and are shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does JSON_TABLE produce?

    JSON_TABLE is a table function. The row path selects the repeating elements, and the COLUMNS clause pulls typed values out of each one.
  2. 2. Which MySQL index type lets MEMBER OF search an array of tags quickly?

    Multi-valued indexes (8.0.17+) store one entry per array element and serve MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS.
  3. 3. You created the index ON users ((CAST(profile->>'$.age' AS UNSIGNED))). Which query can use it?

    A functional index is used only when the query contains the same expression, including the cast.
  4. 4. What is the difference between JSON_CONTAINS(tags, '["a","b"]') and JSON_OVERLAPS(tags, '["a","b"]')?

    JSON_CONTAINS asks whether the candidate is entirely inside the target. JSON_OVERLAPS asks whether the two arrays share any element.
  5. 5. Why might a child table beat JSON_TABLE for order lines?

    JSON_TABLE parses documents at query time. When array elements are queried all day, normal rows with indexes are faster and safer.

Summary

  • JSON_TABLE(doc, path COLUMNS (...)) turns array elements into rows. Use FOR ORDINALITY, DEFAULT ... ON EMPTY, EXISTS PATH and NESTED PATH as needed, and LEFT JOIN ... ON TRUE to keep empty arrays.
  • MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS test array membership.
  • A multi-valued index (CAST(path AS type ARRAY), 8.0.17+) stores one entry per element and makes those tests fast.
  • For scalar keys, index a generated column or a functional expression, and repeat the exact expression in queries.
  • JSON_ARRAYAGG and JSON_OBJECTAGG build documents from groups. Hot keys deserve real columns.

Next: Full-Text Search.