MySQL JSON columns: functions, indexes and generated columns

MySQL has had a real JSON type since 5.7, and in 8.4 it is a perfectly good place for data whose shape varies: product attributes, settings blobs, webhook payloads, feature flags. What it is not is a free pass on schema design. A JSON column cannot be indexed directly, a query that filters on a field inside it scans the whole table, and every value comes back with a type you have to think about. The answer to all three is the same feature: a generated column that pulls one field out of the document, with an ordinary index on it. That combination - flexible document, indexed fields you actually query - is how to use JSON in MySQL without regretting it.

What the JSON type actually stores#

A JSON column is not text with a check attached. On insert, MySQL parses the document, rejects it if it is invalid, and stores it in a binary format that allows reading one member without parsing the whole thing.

CREATE TABLE products (  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  sku        VARCHAR(40) NOT NULL UNIQUE,  attributes JSON NOT NULL DEFAULT (JSON_OBJECT()),  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO products (sku, attributes) VALUES  ('MUG-01', '{"colour": "blue", "capacity_ml": 350, "tags": ["kitchen", "gift"]}'),  ('TEE-02', '{"colour": "black", "sizes": ["S", "M", "L"], "price": 19.5}');

Some consequences of storing it parsed:

• Invalid JSON is an error, not a warning. INSERT ... VALUES ('{colour: blue}') fails with error 3140, Invalid JSON text. That is a feature: a TEXT column would store the garbage.
• The document is normalised. Whitespace is discarded, object keys are sorted, and if a key appears twice the last value wins. You do not get your original text back byte for byte, which matters if you store signed payloads - keep those in a TEXT or BLOB column instead.
• Size is bounded by `max_allowed_packet`, 64 MB by default in 8.4. In practice documents over a few hundred kilobytes are a design problem long before they hit that.
• A JSON column cannot have a literal default. The expression default DEFAULT (JSON_OBJECT()) in the example above, with its parentheses, has been allowed since 8.0.13.

JSON_STORAGE_SIZE(attributes) tells you how many bytes a value takes on disk, which is a quick way to find the rows somebody has been stuffing.

Reading values: paths, -> and ->>#

Fields are addressed with a path expression. $ is the whole document, $.colour a member, $.tags[0] an array element, $.tags[last] the last one, $.tags[*] every element and $**.price any price at any depth. A member name with spaces or dashes is quoted: $."list-price".

Two operators cover most reading:

SELECT sku,       attributes->>'$.colour'      AS colour,       attributes->'$.capacity_ml'  AS capacity,       JSON_LENGTH(attributes, '$.tags') AS tag_countFROM productsWHERE attributes->>'$.colour' = 'blue';

Which operator to use is not a matter of taste. -> returns a JSON value, so it compares as JSON: numbers as numbers, strings as strings. ->> returns text, so attributes->>'$.price' > 9 compares strings unless MySQL converts, and '10' < '9' as text. Use ->> for strings you display or compare to a string; use -> (or an explicit CAST) for numbers.

Searching inside arrays and objects#

Filtering on array membership is where JSON earns its place, because the alternative is a join table.

-- Products tagged 'gift'SELECT sku FROM products WHERE 'gift' MEMBER OF (attributes->'$.tags');-- Contains all of these valuesSELECT sku FROM productsWHERE JSON_CONTAINS(attributes->'$.sizes', '["M", "L"]');-- Contains any of these valuesSELECT sku FROM productsWHERE JSON_OVERLAPS(attributes->'$.tags', '["gift", "sale"]');-- Find the path to a valueSELECT JSON_SEARCH(attributes, 'one', 'kitchen') FROM products;  -- "$.tags[0]"

MEMBER OF and JSON_OVERLAPS arrived in 8.0.17 and are the two to remember, because they are the ones a multi-valued index (below) can serve. JSON_CONTAINS also uses that index.

When you need to treat a JSON array as rows - to join against it, group by its elements or export it - JSON_TABLE turns a document into a derived table:

SELECT p.sku, t.tagFROM products p,     JSON_TABLE(p.attributes, '$.tags[*]'       COLUMNS (tag VARCHAR(40) PATH '$')) AS t;

The reverse direction, rows into JSON, is JSON_OBJECT, JSON_ARRAY, and the aggregates JSON_ARRAYAGG and JSON_OBJECTAGG, which are the cleanest way to build an API response in one query: SELECT JSON_ARRAYAGG(JSON_OBJECT('sku', sku, 'colour', attributes->>'$.colour')) FROM products.

Updating part of a document#

You do not have to read, modify and write back the whole document in application code. MySQL has functions that return a modified copy:

UPDATE productsSET attributes = JSON_SET(attributes, '$.price', 21.0, '$.on_sale', true)WHERE sku = 'TEE-02';UPDATE productsSET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'sale')WHERE sku = 'MUG-01';

Doing it in SQL is not only shorter; it is safer under concurrency. Two requests that each read the document, change a different field and write it back will lose one change. Two UPDATE ... JSON_SET statements on the same row are serialised by the row lock and both survive. MySQL transactions, locking and deadlocks has the general version of that problem.

When JSON_SET, JSON_REPLACE or JSON_REMOVE update a column in place (SET col = JSON_SET(col, ...)) and the new value is not larger than the old, InnoDB can perform a partial update rather than rewriting the whole value. With binlog_row_value_options = PARTIAL_JSON, the binary log also records only the change. That is an optimisation you get for free by updating in SQL, and not at all by rewriting the document from the application.

JSON_MERGE_PRESERVE (and its deprecated alias JSON_MERGE) concatenates arrays and combines duplicate keys into arrays; JSON_MERGE_PATCH replaces values, which is almost always what an API "patch" means.

Indexing JSON with generated columns#

A JSON column cannot be in an index. WHERE attributes->>'$.colour' = 'blue' on a million rows reads a million documents. The fix is a generated column: a column whose value is computed from an expression over other columns in the same row.

ALTER TABLE products  ADD COLUMN colour VARCHAR(30)    GENERATED ALWAYS AS (attributes->>'$.colour') VIRTUAL,  ADD INDEX idx_colour (colour);

A VIRTUAL column (the default) is computed when the row is read and takes no space in the table; InnoDB can still put it in a secondary index, where the computed value is stored. A STORED column is computed on write and kept in the row, which costs space and makes it usable in a primary key. For indexing JSON fields, virtual is the normal choice.

EXPLAIN SELECT sku FROM products WHERE colour = 'blue';-- type: ref, key: idx_colour

Query the generated column by name. The optimizer can also recognise the original expression and use the index for WHERE attributes->>'$.colour' = 'blue', but only when the expression, its type and its collation match the column definition exactly; naming the column is never ambiguous. MySQL indexes and EXPLAIN covers reading the plan.

Things that catch people:

• The type matters. VARCHAR(30) AS (attributes->>'$.colour') stores text. For a number, cast: price DECIMAL(10,2) AS (CAST(attributes->>'$.price' AS DECIMAL(10,2))). A document whose price is not numeric then fails on insert, which is often exactly the validation you wanted.
• A value too long for the column is an error in strict mode, which is the 8.4 default. Size the generated column for the data, or insert fails on the one row with a long value.
• Only deterministic expressions are allowed - no NOW(), RAND(), user variables or subqueries.
• Adding a virtual column is cheap; building its index is not. The column is a metadata change, but the index has to read every row. InnoDB builds it online, allowing reads and writes meanwhile, but on a large table it still takes time and I/O. Do it at a quiet hour.

Functional and multi-valued indexes#

Since 8.0.13 you can skip the named column and index the expression directly. MySQL creates a hidden virtual column for you. The string case has a collation trap: CAST(... AS CHAR) yields the default collation, while ->> yields utf8mb4_bin, and an index built on one is not used for a comparison with the other. The MySQL manual's own fix is to state the collation in the index:

ALTER TABLE products  ADD INDEX idx_material ((CAST(attributes->>'$.material' AS CHAR(30))                           COLLATE utf8mb4_bin));SELECT sku FROM products WHERE attributes->>'$.material' = 'steel';

It works, but the named generated column is easier to read, easier to query and visible to everybody who opens the schema. Prefer it unless you have a reason not to.

Arrays need a different kind of index. Since 8.0.17 a multi-valued index stores one index entry per array element, so MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS can use it:

CREATE TABLE customers (  id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  name     VARCHAR(100) NOT NULL,  profile  JSON NOT NULL,  INDEX idx_zips ((CAST(profile->'$.zipcodes' AS UNSIGNED ARRAY))));SELECT name FROM customers WHERE 94507 MEMBER OF (profile->'$.zipcodes');

The cast type is part of the index - UNSIGNED ARRAY, SIGNED ARRAY, CHAR(n) ARRAY, DATE ARRAY and a few others - and the values in the documents must convert to it. A multi-valued index can have only one array key part, cannot be a primary or covering index, and is used for filtering, not for sorting.

Converting an existing TEXT column to JSON#

The common real-world starting point is not a fresh table but an old one, where somebody stored JSON in a TEXT or LONGTEXT column years ago. Converting it buys you validation, the binary format and every function above. The conversion fails on the first invalid row, so find those first:

SELECT id, LEFT(settings, 80) AS previewFROM accountsWHERE settings IS NOT NULL AND JSON_VALID(settings) = 0;

Expect three kinds of result. Empty strings, which are not valid JSON - decide whether they mean NULL or {} and update them. Documents with single quotes or trailing commas, written by hand or by a serialiser that was not really producing JSON - fix them in the application that wrote them, then in the data. And PHP serialize() output or similar, which means the column was never JSON and needs a script, not SQL.

Once the query returns nothing, convert in one statement:

UPDATE accounts SET settings = '{}' WHERE settings = '';ALTER TABLE accounts MODIFY settings JSON NULL;

Changing a column's type rebuilds the table: InnoDB copies every row, and concurrent writes are blocked while it does (ALGORITHM=COPY). On a table of a few hundred megabytes that is seconds to minutes; on something larger, plan a maintenance window, or add a new JSON column, backfill it in batches, and switch the application over before dropping the old one. Take a dump first either way - mysqldump backup and restore covers doing that without locking the table.

One side effect to check afterwards: because documents are normalised on the way in, key order and whitespace change. Anything that compared the raw text - a cache key built from the string, a checksum, a test fixture - will see different bytes for the same data.

Sorting and grouping by JSON fields#

ORDER BY attributes->'$.price' works, but it sorts JSON values, and JSON comparison has its own rules: numbers sort as numbers, strings as strings, and values of different JSON types are ordered by type before value. If some documents store the price as 19.5 and others as "19.50", they will not interleave the way you expect. Cast explicitly when the order matters - ORDER BY CAST(attributes->>'$.price' AS DECIMAL(10,2)) - and better, sort by a generated column with that cast, which also lets an index provide the order instead of a filesort.

Grouping is the same: GROUP BY attributes->>'$.colour' groups by text, so "Blue" and "blue" are separate groups under the binary collation that ->> returns. A generated VARCHAR column with the default case-insensitive collation groups them together. Small differences like this are a good argument for routing every field you report on through a generated column with a declared type and collation.

Validating documents and knowing when not to use JSON#

A JSON column accepts any valid document. If your application assumes price is always a number, enforce it, because the one row where it is a string will turn up in a report six months from now. JSON_SCHEMA_VALID (8.0.17) checks a document against a JSON Schema, and a CHECK constraint makes MySQL enforce it on every write:

ALTER TABLE products ADD CONSTRAINT attributes_shape CHECK (  JSON_SCHEMA_VALID('{    "type": "object",    "properties": {      "colour": {"type": "string"},      "price":  {"type": "number", "minimum": 0},      "tags":   {"type": "array", "items": {"type": "string"}}    }  }', attributes));

The violation shows up as error 3819, Check constraint 'attributes_shape' is violated. Generated columns with casts are the lighter-weight version of the same idea.

Then the honest part. JSON is the wrong choice when:

• You join on it. Foreign keys cannot point into a document, so referential integrity is gone. A user_id inside JSON is a bug waiting for a deleted user.
• Every row has the same fields. Then they are columns. Columns are smaller, typed, indexable without ceremony and visible in the schema.
• You update one counter inside a large document many times a second. Each update locks the row and, if the value grows, rewrites the document.
• You aggregate across it constantly. SUM(CAST(attributes->>'$.price' AS DECIMAL)) over the whole table is a full scan however you index it.

A reasonable rule: fixed, queried and joined fields are columns; optional, sparse or client-defined fields go in one JSON column; any field from the JSON that becomes important enough to filter on gets promoted to a generated column with an index. If you are deciding between MySQL's JSON and a document database, MySQL vs PostgreSQL covers how PostgreSQL's jsonb and GIN indexes compare, and Postgres or MongoDB the document-store side.

FAQ#

Is a JSON column slower than normal columns?

Reading one field from a JSON column is slightly slower than reading a column, and filtering on an unindexed field is much slower because it scans. With a generated column and index on the fields you filter by, queries behave like any indexed column. The storage overhead of the binary format is modest but real - keys are stored in every row.

Can I put a foreign key on a value inside JSON?

Not directly. You can add a STORED generated column extracting the value and put the foreign key on that column, which works for a single scalar. If you find yourself doing that, the value probably belongs in a normal column.

Why does my query return "blue" with quotes?

You used -> or JSON_EXTRACT, which return JSON values, and a JSON string includes its quotes. Use ->> or wrap the expression in JSON_UNQUOTE to get plain text.

Does MySQL JSON work with ORMs?

Yes. Laravel's Eloquent casts JSON columns to arrays and supports where('attributes->colour', 'blue'), Django's JSONField supports MySQL, and SQLAlchemy has a JSON type. What the ORM cannot do for you is create the generated column and index - add those in a migration.

How do I find documents missing a field?

WHERE NOT JSON_CONTAINS_PATH(attributes, 'one', '$.price'). Note that attributes->>'$.price' IS NULL also matches documents where the field is missing, but not ones where it is an explicit JSON null, which return the string null.