Working with JSON in MySQL

MySQL 5.7 introduced a native JSON data type and a growing set of functions for storing, querying, and manipulating JSON documents. This lets you combine the structure of relational tables with the flexibility of semi-structured data -- useful for configuration objects, event payloads, and API responses that vary in shape.

Storing JSON Data

Declare a column as JSON. MySQL validates the format on insert, so invalid JSON is rejected automatically.

CREATE TABLE events (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  metadata JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name, metadata) VALUES
('signup', '{"source": "google", "campaign": "spring2025", "device": "mobile"}'),
('purchase', '{"product_id": 42, "amount": 29.99, "currency": "USD"}');

Extracting Values with JSON_EXTRACT

JSON_EXTRACT() retrieves a value at a given path. The shorthand -> operator and the unquoting ->> operator are convenient alternatives.

-- All three return the same value
SELECT
  JSON_EXTRACT(metadata, '$.source')  AS source_v1,
  metadata -> '$.source'              AS source_v2,
  metadata ->> '$.source'             AS source_v3  -- unquoted string
FROM events
WHERE name = 'signup';

Filtering by JSON Values

Use the arrow operators in a WHERE clause to filter rows based on JSON content. The unquoting operator->> returns a plain string, which is easier to compare.

SELECT *
FROM events
WHERE metadata ->> '$.source' = 'google';

SELECT *
FROM events
WHERE JSON_EXTRACT(metadata, '$.amount') > 20;

Modifying JSON with JSON_SET and JSON_REPLACE

JSON_SET() adds or replaces a value at a path. JSON_REPLACE() only updates existing keys. JSON_REMOVE() deletes a key entirely.

-- Add a new key
UPDATE events
SET metadata = JSON_SET(metadata, '$.processed', true)
WHERE id = 1;

-- Remove a key
UPDATE events
SET metadata = JSON_REMOVE(metadata, '$.campaign')
WHERE id = 1;

Creating JSON on the Fly

Build JSON objects and arrays from relational data using JSON_OBJECT() and JSON_ARRAY(). These are useful for API responses built directly in SQL.

SELECT JSON_OBJECT(
  'id', id,
  'name', first_name,
  'email', email
) AS user_json
FROM users
LIMIT 5;

SELECT JSON_ARRAYAGG(
  JSON_OBJECT('id', id, 'name', product_name)
) AS products_json
FROM products
WHERE category = 'Electronics';

Indexing JSON with Generated Columns

You cannot index a JSON column directly, but you can create a virtual generated column that extracts a specific path and index that column. This gives you the best of both worlds: flexible storage and fast lookups.

ALTER TABLE events
ADD COLUMN source VARCHAR(50)
  GENERATED ALWAYS AS (metadata ->> '$.source') VIRTUAL;

CREATE INDEX idx_events_source ON events (source);

-- Now this query uses the index
SELECT * FROM events WHERE source = 'google';

When to Use JSON

JSON columns work well for variable-schema data, configuration blobs, and audit payloads. Avoid storing data in JSON that you frequently filter, join, or aggregate -- that data belongs in proper relational columns. When in doubt, normalize first and reach for JSON only when the schema truly cannot be predicted ahead of time.