MySQL JSON Columns: Operators, Generated Column Indexes, and Partial Updates

CREATE TABLE products (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(255),
  metadata JSON,
  specs    JSON
);

INSERT INTO products (name, metadata, specs) VALUES
('Widget Pro',
 '{"sku": "WP-001", "tags": ["new", "sale"]}',
 '{"weight_kg": 1.5, "color": "blue"}');

-- Extract value (returns JSON)
SELECT metadata->'$.sku' AS sku FROM products;

-- Extract value as text (unquoted)
SELECT metadata->>'$.sku' AS sku FROM products;

-- Nested path
SELECT specs->>'$.color' FROM products WHERE id = 1;

-- Array element
SELECT metadata->>'$.tags[0]' FROM products;

-- WHERE on JSON value
SELECT * FROM products WHERE specs->>'$.color' = 'blue';

-- JSON_CONTAINS for array membership
SELECT * FROM products
WHERE JSON_CONTAINS(metadata->'$.tags', '"sale"');

-- JSON_SEARCH
SELECT * FROM products
WHERE JSON_SEARCH(metadata, 'one', 'new') IS NOT NULL;

-- Create generated column and index it
ALTER TABLE products
  ADD COLUMN sku VARCHAR(50)
    GENERATED ALWAYS AS (metadata->>'$.sku') STORED;

CREATE INDEX idx_products_sku ON products (sku);

-- Now queries on sku use the index
SELECT * FROM products WHERE sku = 'WP-001';

-- Update a single JSON key without rewriting the whole doc
UPDATE products
SET specs = JSON_SET(specs, '$.color', 'red')
WHERE id = 1;

-- Remove a key
UPDATE products
SET metadata = JSON_REMOVE(metadata, '$.tags[1]')
WHERE id = 1;

-- Append to array
UPDATE products
SET metadata = JSON_ARRAY_APPEND(metadata, '$.tags', 'featured')
WHERE id = 1;