JSON

1. Overview

MySQL provides full native support for the JSON data type, including efficient binary storage, JSON manipulation functions, and advanced querying tools such as JSON_TABLE.

2. Creating tables with JSON columns

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(50) NOT NULL,
  attrs JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (sku, attrs) VALUES
('SKU-100', JSON_OBJECT('color', 'red', 'weight', 2.5, 'price_points', JSON_ARRAY(JSON_OBJECT('amount', 90, 'currency', 'USD')))),
('SKU-200', JSON_OBJECT('color', 'blue', 'weight', 3.2, 'price_points', JSON_ARRAY(JSON_OBJECT('amount', 120, 'currency', 'USD'))));

3. Accessing and reading JSON values

SELECT
  sku,
  attrs->>'$.color' AS color,
  JSON_EXTRACT(attrs, '$.weight') AS weight_json
FROM products;

SELECT JSON_PRETTY(attrs) FROM products;

4. Modifying JSON data

-- UPDATE a KEY's value
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green')
WHERE sku = 'SKU-100';

-- Append to a JSON array
UPDATE products
SET attrs = JSON_ARRAY_APPEND(attrs, '$.price_points', JSON_OBJECT('amount', 150, 'currency', 'USD'))
WHERE sku = 'SKU-200';

-- Remove a KEY
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.weight')
WHERE sku = 'SKU-100';

5. Filtering with JSON data

SELECT * FROM products WHERE attrs->>'$.color' = 'blue';

SELECT * FROM products WHERE JSON_CONTAINS(attrs, '"USD"', '$.price_points[*].currency');

SELECT * FROM products WHERE CAST(attrs->>'$.weight' AS DECIMAL(5,2)) > 2.8;

6. Indexing JSON fields

MySQL 8.0 allows indexing JSON paths via generated columns for fast lookups.

ALTER TABLE products
  ADD COLUMN color VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.color'))) STORED,
  ADD COLUMN weight DECIMAL(5,2) AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.weight'))) STORED;

CREATE INDEX idx_color ON products(color);
CREATE INDEX idx_weight ON products(weight);

7. Converting JSON to relational data

MySQL 8.0 introduces JSON_TABLE(), which transforms JSON arrays into relational rows.

SELECT p.sku, jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.price_points[*]'
  COLUMNS (
    idx FOR ORDINALITY,
    amount DECIMAL(10,2) PATH '$.amount',
    currency VARCHAR(10) PATH '$.currency'
  )
) AS jt;

8. Common JSON functions (8.0+)

JSON_OBJECT(KEY, value, ...)
JSON_ARRAY(value, ...)
JSON_SET(json_doc, path, val)
JSON_REPLACE(json_doc, path, val)
JSON_REMOVE(json_doc, path)
JSON_CONTAINS(json_doc, candidate, path)
JSON_SEARCH(json_doc, 'one', 'pattern')
JSON_PRETTY(json_doc)
JSON_TABLE(json_doc, path COLUMNS (...))