JSON columns and multi-valued indexes

MySQL's JSON support covers flexible attributes well, but indexing it needs specific techniques.

The JSON type validates documents on insert and stores them in an optimised binary format.

Reading:

doc->'$.address.city' is shorthand for JSON_EXTRACT(...) and returns JSON (strings keep their quotes).
doc->>'$.address.city' also unquotes, returning plain text — usually what you want.
JSON_CONTAINS(doc, '"vip"', '$.tags') and 'vip' MEMBER OF (doc->'$.tags') test array membership.
JSON_TABLE(doc, '$.items[*]' COLUMNS (...)) turns a JSON array into relational rows you can join and aggregate.

Writing: JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE and JSON_ARRAY_APPEND return a modified document; InnoDB can apply some updates in place (partial updates) to save work.

Indexing: a JSON column can't be indexed directly. Options:

A generated column extracting a scalar, then a normal index: city VARCHAR(50) AS (doc->>'$.address.city') VIRTUAL, KEY (city).
A functional index on a cast expression: INDEX ((CAST(doc->>'$.sku' AS CHAR(20)))).
A multi-valued index on an array: INDEX ((CAST(doc->'$.tags' AS CHAR(20) ARRAY))), used by MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS.

As with any database, promote fields you filter on constantly into real columns.

Example

CREATE TABLE product (
    id   INT PRIMARY KEY,
    doc  JSON NOT NULL,
    brand VARCHAR(50) AS (doc->>'$.brand') VIRTUAL,
    KEY idx_brand (brand),
    INDEX idx_tags ((CAST(doc->'$.tags' AS CHAR(20) ARRAY)))
);
INSERT INTO product (id, doc) VALUES
 (1, '{"brand": "Acme", "tags": ["sale", "new"], "sizes": [{"s": "M", "qty": 3}]}'),
 (2, '{"brand": "Zeta", "tags": ["new"], "sizes": [{"s": "L", "qty": 0}]}');

SELECT id, doc->'$.brand', doc->>'$.brand' FROM product WHERE brand = 'Acme';
-- 1 | "Acme" | Acme
SELECT id FROM product WHERE 'sale' MEMBER OF (doc->'$.tags');   -- 1 (uses idx_tags)

SELECT p.id, s.size, s.qty
FROM product p, JSON_TABLE(p.doc, '$.sizes[*]'
     COLUMNS (size VARCHAR(5) PATH '$.s', qty INT PATH '$.qty')) AS s;
-- 1 | M | 3
-- 2 | L | 0
UPDATE product SET doc = JSON_SET(doc, '$.brand', 'Acme Co') WHERE id = 1;

Remember

->> extracts and unquotes; -> returns JSON
Index JSON via generated columns, functional or multi-valued indexes
JSON_TABLE turns arrays into rows

Watch out for

Comparing doc->'$.name' = 'Ana': -> keeps JSON quoting, so use ->> for text comparisons.

Check yourself

How can you index the elements of a JSON array for MEMBER OF queries?

Official docs: dev.mysql.com/doc/refman/8.4/en/json.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks