JSON columns – useful patterns and pitfalls

JSON columns. Useful patterns and pitfalls. MySQL’s JSON type stores binary-encoded documents with validation on insert. Handy for settings, metadata, and semi-structured data you do not want to explode into dozens of nullable columns.

Defining and inserting JSON

CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(32) NOT NULL,
  metadata JSON,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer_id, status, metadata) VALUES
(42, 'pending', JSON_OBJECT(
  'source', 'web',
  'campaign', 'summer-sale',
  'gift_message', 'Happy birthday'
));

Invalid JSON is rejected at insert time – unlike TEXT where bad JSON sits quietly until something breaks.

Reading and updating paths

SELECT
  id,
  metadata->>'$.source' AS source,
  metadata->'$.campaign' AS campaign_raw
FROM orders
WHERE id = 1;

UPDATE orders
SET metadata = JSON_SET(
  COALESCE(metadata, JSON_OBJECT()),
  '$.shipped_at',
  DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%s')
)
WHERE id = 1;

->> returns unquoted text. -> returns a JSON value (still quoted for strings). Use JSON_SET, JSON_REMOVE, JSON_MERGE_PATCH for partial updates.

Useful patterns

• App settings per tenant – theme, feature flags, limits in one column
• Audit snapshots – store before/after state on change records
• Import staging – raw API payload while normalised columns are populated
• Generated columns – extract hot fields for indexing

ALTER TABLE orders
ADD COLUMN order_source VARCHAR(32)
  GENERATED ALWAYS AS (metadata->>'$.source') STORED,
ADD INDEX idx_orders_source (order_source);

SELECT id FROM orders WHERE order_source = 'web';

STORED generated columns are indexed like normal columns. VIRTUAL columns compute on read – cheaper writes, no index on the virtual expression in older patterns.

Querying inside JSON

SELECT id, customer_id
FROM orders
WHERE metadata->>'$.source' = 'web'
  AND CAST(metadata->>'$.priority' AS UNSIGNED) >= 5;

-- JSON_CONTAINS for array membership
SELECT id FROM orders
WHERE JSON_CONTAINS(metadata, '"vip"', '$.tags');

Without a generated column or functional index, filters on JSON paths scan the table. Fine for small sets; not for millions of rows.

Pitfalls

• JSON as a junk drawer – if you always filter on a field, promote it to a real column
• Schema drift – no enforced shape; document expected keys in app validation
• Large documents – big JSON blobs bloat pages and buffer pool; normalise repeated structures
• Updates rewrite the whole document – JSON_SET still replaces the stored value internally
• NULL vs missing key – JSON_EXTRACT on missing path returns NULL; handle both in app logic
• Cross-row JSON joins – possible but awkward; relational data belongs in relational tables

-- Validate shape in app; optional CHECK in MySQL 8.0.16+
ALTER TABLE orders ADD CONSTRAINT chk_metadata_object
CHECK (JSON_TYPE(metadata) IN ('OBJECT', 'NULL'));

Rule of thumb

Use JSON for data you store and occasionally read whole, or for keys that change rarely and are not join/filter heavy. Use normal columns for anything in WHERE, JOIN, ORDER BY, or foreign keys. The intermediate normalisation tutorial still applies – JSON is not permission to skip thinking.

The next tutorial covers security hardening – users, grants, and least privilege.