Storing JSON in MySQL 5.7 and 8.0

2020-01-23 · mysql, json, schema

Developers keep bringing me data that doesn't fit a fixed schema: per-customer settings, third-party API responses, product attributes that differ by category. Before 5.7 the choice was TEXT you couldn't query or a dozen nullable columns, and I hated both. The native JSON type (5.7.8) validates on insert, stores a binary format that gives direct access to nested values, and has functions to query and modify them. 8.0 added JSON_TABLE, multi-valued indexes and partial updates. Here's how I store, query, index and update JSON without turning the database into a blob store.

Define the column

CREATE TABLE products (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku   VARCHAR(40) NOT NULL UNIQUE,
  attrs JSON NOT NULL
) ENGINE=InnoDB;

INSERT INTO products (sku, attrs) VALUES
 ('LAP-001', '{"brand":"Lenovo","ram_gb":16,"tags":["laptop","business"],"ports":{"usb_c":2,"hdmi":1}}'),
 ('LAP-002', '{"brand":"Dell","ram_gb":8,"tags":["laptop"],"ports":{"usb_c":1}}');

Invalid documents are rejected at insert time, which is already an improvement over TEXT:

INSERT INTO products (sku, attrs) VALUES ('BAD', '{"brand": }');
-- ERROR 3140 (22032): Invalid JSON text: "Missing a value for object member." at position 10 in value for column 'products.attrs'.

A JSON column cannot have a default value on 5.7; on 8.0.13 and later you can use an expression default such as DEFAULT (JSON_OBJECT()).

Query values

Use JSON_EXTRACT or its shorthand ->, and ->> (JSON_UNQUOTE(JSON_EXTRACT(...))) to get a plain string instead of a quoted JSON string:

SELECT sku, attrs->'$.brand' AS brand_json, attrs->>'$.brand' AS brand, attrs->'$.ports.usb_c' AS usbc
FROM products;

SELECT sku FROM products WHERE attrs->>'$.brand' = 'Lenovo';
SELECT sku FROM products WHERE attrs->'$.ram_gb' >= 16;
SELECT sku FROM products WHERE JSON_CONTAINS(attrs->'$.tags', '"business"');
SELECT sku FROM products WHERE JSON_SEARCH(attrs, 'one', 'laptop', NULL, '$.tags') IS NOT NULL;

Path expressions start with $, use .key for object members and [n] for array elements; $.tags[0] is the first tag. JSON_KEYS(attrs->'$.ports') lists the member names, JSON_LENGTH(attrs->'$.tags') the array size.

Index what you query

You cannot index a JSON column directly. The 5.7 approach, which still works on 8.0, is a generated column with an ordinary index:

ALTER TABLE products
  ADD COLUMN brand VARCHAR(50) AS (attrs->>'$.brand') VIRTUAL,
  ADD INDEX idx_brand (brand);

EXPLAIN SELECT sku FROM products WHERE attrs->>'$.brand' = 'Lenovo';

The optimizer recognizes that the expression in the WHERE matches the generated column and uses idx_brand even though the query does not name the column. VIRTUAL columns cost no storage; the index does.

On 8.0.13 and later you can skip the column and create a functional index directly:

ALTER TABLE products ADD INDEX idx_ram ((CAST(attrs->>'$.ram_gb' AS UNSIGNED)));

For arrays, 8.0.17 introduced multi-valued indexes, which index every element:

ALTER TABLE products ADD INDEX idx_tags ((CAST(attrs->'$.tags' AS CHAR(32) ARRAY)));
SELECT sku FROM products WHERE 'business' MEMBER OF (attrs->'$.tags');

Modify documents in place

UPDATE products SET attrs = JSON_SET(attrs, '$.ram_gb', 32, '$.ports.hdmi', 2) WHERE sku = 'LAP-002';
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.tags[1]') WHERE sku = 'LAP-001';
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.tags', 'refurbished') WHERE sku = 'LAP-002';
UPDATE products SET attrs = JSON_MERGE_PATCH(attrs, '{"warranty_months": 24}');

JSON_SET creates or replaces, JSON_INSERT only creates, JSON_REPLACE only replaces. On 8.0, updates that use JSON_SET, JSON_REPLACE or JSON_REMOVE and do not grow the document can be applied as partial updates, and with binlog_row_value_options = PARTIAL_JSON the binary log carries only the diff – a large saving for big documents on replicated servers.

JSON_TABLE turns JSON into rows (8.0 only)

SELECT p.sku, t.tag
FROM products p,
     JSON_TABLE(p.attrs, '$.tags[*]' COLUMNS (tag VARCHAR(32) PATH '$')) AS t;

This is the tool for reporting on arrays: it produces one row per element, which you can then GROUP BY or join normally. On 5.7 the same needs a numbers table and JSON_EXTRACT with a computed index, which is ugly enough that I usually do it in application code there.

Quick checks

SELECT JSON_VALID(attrs) FROM products;                -- all 1
SELECT JSON_STORAGE_SIZE(attrs) FROM products;         -- bytes per document
EXPLAIN SELECT sku FROM products WHERE attrs->>'$.brand' = 'Dell';   -- key: idx_brand

If EXPLAIN shows type: ALL, the expression in the query does not match the generated column definition exactly (for example JSON_UNQUOTE(JSON_EXTRACT(attrs,'$.brand')) versus attrs->>'$.brand' is fine, but a different path or a CAST is not).

Gotchas

ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 4. – Usually a missing $ at the start of the path or an unquoted key with a special character; quote it as $."key-with-dash".
ERROR 3105 (HY000): The value specified for generated column 'brand' in table 'products' is not allowed. – You tried to INSERT into a generated column; leave it out of the column list.
Comparisons against -> return unexpected results because the value is still JSON ("Lenovo" with quotes). Use ->> or JSON_UNQUOTE when comparing to a plain string.
A JSON column has the same 1 GB (max_allowed_packet) practical limit as LONGBLOB, but performance drops well before that. Documents in the tens of kilobytes are fine; documents in the megabytes belong in object storage with a reference in the row.

JSON columns are not a reason to stop designing tables. My rule: anything I filter, join or aggregate on often gets a real column (or an indexed generated one). Only the genuinely variable stuff goes in the document.