JSON Columns

JSON Data Type

MySQL JSON type stores documents in binary format with automatic validation on insert. It is more efficient than TEXT with JSON constraints and provides rich query functions.

Use JSON for flexible attributes (metadata, settings, feature flags) while keeping core relational columns normalized for frequent filters and joins.

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

INSERT INTO products (name, attributes) VALUES
  ('Laptop', JSON_OBJECT('ram_gb', 16, 'storage', '512GB SSD', 'tags', JSON_ARRAY('electronics', 'computers')));

Extracting Values

JSON_EXTRACT (or -> operator) retrieves values by path. ->> returns unquoted scalar results. JSON_UNQUOTE removes quotes from string results.

Path syntax uses $.field and $.array[0]. Missing paths return NULL—handle in COALESCE for defaults.

SELECT
  name,
  attributes->>'$.ram_gb' AS ram,
  JSON_EXTRACT(attributes, '$.tags[0]') AS first_tag
FROM products
WHERE attributes->>'$.storage' LIKE '%SSD%';

Modifying JSON

JSON_SET, JSON_INSERT, JSON_REPLACE, and JSON_REMOVE update documents. JSON_MERGE_PATCH combines objects. Update statements can modify JSON columns in place without fetching to application.

Validate shape in application before partial updates to avoid inconsistent documents.

• JSON_ARRAY_APPEND adds elements to arrays
• Deep merges require careful path planning
• Large JSON documents increase row size and buffer pool pressure

UPDATE products
SET attributes = JSON_SET(attributes, '$.ram_gb', 32)
WHERE id = 1;

JSON Indexes

Functional indexes (MySQL 8.0.13+) index expressions on JSON paths for faster filters. Multi-valued indexes (8.0.17+) index array elements for membership queries.

Without indexes, JSON path filters cause full table scans—add indexes on hot query paths.

CREATE INDEX idx_products_ram ON products ((CAST(attributes->>'$.ram_gb' AS UNSIGNED)));

SELECT * FROM products WHERE attributes->>'$.ram_gb' = '16';

JSON vs Normalized Design

JSON suits optional heterogeneous attributes and rapid schema iteration. Relational columns suit filtered, joined, constrained data with foreign keys.

Anti-pattern: storing entire relational graphs in one JSON blob loses integrity and query flexibility. Hybrid models—stable columns plus JSON extension—balance both worlds.

SELECT id, name FROM products
WHERE JSON_CONTAINS(attributes, '"sale"', '$.tags');