JSON in MySQL: Columns, Functions and Indexing

MySQL's JSON column type validates documents on insert and stores them in an efficient binary format. It's handy for flexible attributes alongside normal columns.

Storing and reading

CREATE TABLE products (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  attrs JSON NOT NULL
);

INSERT INTO products (name, attrs)
VALUES ('Phone', '{"brand": "Acme", "storage_gb": 128, "colors": ["black", "blue"]}');

SELECT name,
       attrs->'$.brand'       AS brand_json,   -- "Acme" (JSON, with quotes)
       attrs->>'$.brand'      AS brand,        -- Acme (unquoted text)
       attrs->>'$.colors[0]'  AS first_color
FROM products;

Filtering

SELECT name FROM products WHERE attrs->>'$.brand' = 'Acme';
SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.colors', '"blue"');
SELECT name FROM products WHERE 'blue' MEMBER OF (attrs->'$.colors');
SELECT name FROM products WHERE CAST(attrs->>'$.storage_gb' AS UNSIGNED) >= 128;

Updating

UPDATE products SET attrs = JSON_SET(attrs, '$.storage_gb', 256) WHERE id = 1;
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.colors', 'green') WHERE id = 1;
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.colors[0]') WHERE id = 1;

Indexing JSON fields

JSON columns can't be indexed directly. Index an extracted value instead:

-- Option 1: a generated column
ALTER TABLE products
  ADD COLUMN brand VARCHAR(100) AS (attrs->>'$.brand') VIRTUAL,
  ADD INDEX idx_products_brand (brand);

-- Option 2: a functional index
CREATE INDEX idx_products_storage ON products ((CAST(attrs->>'$.storage_gb' AS UNSIGNED)));

-- Option 3: multi-valued index for arrays (used by MEMBER OF / JSON_CONTAINS)
CREATE INDEX idx_products_colors ON products ((CAST(attrs->'$.colors' AS CHAR(30) ARRAY)));

Turning JSON into rows

SELECT p.name, c.color
FROM products p,
     JSON_TABLE(p.attrs, '$.colors[*]' COLUMNS (color VARCHAR(30) PATH '$')) AS c;

Use JSON for genuinely variable data. Fields you always query, join or constrain are better as regular columns.