JSON in SQL

MySQL's JSON type, extraction with JSON_EXTRACT, -> and ->>, rows from documents with JSON_TABLE, and when a column is the better answer.

SyntaxJSON_EXTRACT(json_doc, path[, path] ...) -- shorthand: json_doc -> path; unquoted: json_doc ->> path

The JSON type stores a validated document in a binary format with sorted keys, so reading one member does not reparse the whole text. Storage is roughly that of LONGBLOB and the maximum size is bounded by max_allowed_packet. Use it for genuinely open-ended data: per-type event payloads, third-party webhook bodies, user preferences whose keys you do not control.

Reading values

JSON_EXTRACT(json_doc, path[, path] ...) returns a JSON value. Two operators are pure shorthand for it:

payload ->  '$.plan'   =  JSON_EXTRACT(payload, '$.plan')
payload ->> '$.plan'   =  JSON_UNQUOTE(JSON_EXTRACT(payload, '$.plan'))

The first gives back "gold" with its JSON quotes; the second gives the text gold. JSON_VALUE(doc, path RETURNING type) extracts a scalar with a real SQL type and ON EMPTY / ON ERROR handling; without RETURNING it returns VARCHAR(512).

Documents into rows

JSON_TABLE() turns an array into a derived table you can join to. Its columns are name FOR ORDINALITY, name type PATH ..., name type EXISTS PATH ..., and NESTED PATH ... COLUMNS (...) for arrays inside arrays. The table alias is required.

Indexing

Three routes: index a generated column that extracts the scalar; a functional index on the expression; or a multi-valued index over an array, which MEMBER OF(), JSON_CONTAINS() and JSON_OVERLAPS() can use.

When not to use JSON

If every row has the field and you filter, sort, join or aggregate on it, it is a column. JSON cannot be a foreign key, carries no per-key statistics for the optimizer, and hides shape errors until read time.

Example

Example · sql
CREATE TABLE events (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    type       VARCHAR(40) NOT NULL,   -- present on every row: a real column
    created_at DATETIME(6) NOT NULL,
    payload    JSON        NOT NULL,   -- shape varies by type

    -- JSON is not indexable on its own: extract, then index the extraction.
    user_id BIGINT UNSIGNED AS (CAST(payload->>'$.user.id' AS UNSIGNED)) VIRTUAL,
    KEY idx_type_created (type, created_at),
    KEY idx_user (user_id, created_at)
) ENGINE=InnoDB;

INSERT INTO events (type, created_at, payload) VALUES
('signup', NOW(6), '{"user": {"id": 42, "plan": "gold"}, "tags": ["eu", "beta"]}');

-- The two spellings on each row are the same expression.
SELECT
    JSON_EXTRACT(payload, '$.user.plan')                AS a,  -- "gold"
    payload->'$.user.plan'                              AS b,  -- "gold"
    JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user.plan'))  AS c,  -- gold
    payload->>'$.user.plan'                             AS d   -- gold
FROM events
WHERE user_id = 42;   -- uses idx_user, unlike a raw JSON_EXTRACT filter

When to use it

  • Store webhook or event payloads whose keys differ per provider, then index the two fields you actually query.
  • Expand an array of order line items into rows so it can be joined to the products table.
  • Keep user preferences in one column instead of adding a nullable column for every new setting.
  • Query an audit trail of before/after snapshots that must keep whatever shape the row had at the time.

More examples

JSON_TABLE: one row per array element

This is how a stored array joins to the rest of the schema — aggregate it, join it to products, or validate it. ERROR ON ERROR on the sku column turns a malformed document into a loud failure instead of a silent NULL, which is usually what you want when the field is supposed to be mandatory.

Example · sql
-- payload = {"order": 8891,
--            "items": [{"sku":"A1","qty":2,"gift":true},{"sku":"B7","qty":1}]}
SELECT e.id, jt.pos, jt.sku, jt.qty, jt.is_gift
FROM events AS e,
     JSON_TABLE(
         e.payload, '$.items[*]'
         COLUMNS (
             pos     FOR ORDINALITY,                  -- 1, 2, 3 within the document
             sku     VARCHAR(20) PATH '$.sku' ERROR ON ERROR,
             qty     INT         PATH '$.qty' DEFAULT '0' ON EMPTY,
             is_gift INT         EXISTS PATH '$.gift' -- 1 if the key is there, else 0
         )
     ) AS jt                                          -- the alias is mandatory
WHERE e.type = 'order_placed';

A multi-valued index over an array

The alternative to a multi-valued index is a join table, which is usually still the better design for a many-to-many relationship. Use this when the array genuinely belongs to the document and you only ever ask membership questions. Creating one is not an online operation, so it rebuilds the table.

Example · sql
-- One multi-valued key part per index; it serves MEMBER OF, JSON_CONTAINS
-- and JSON_OVERLAPS, but cannot cover a query or do a range scan.
ALTER TABLE events
    ADD INDEX idx_viewers ((CAST(payload->'$.viewer_ids' AS UNSIGNED ARRAY)));

-- Any event whose viewer_ids array contains 42.
SELECT id FROM events
WHERE 42 MEMBER OF (payload->'$.viewer_ids');

-- Contains all of these; JSON_OVERLAPS would mean "any of these".
SELECT id FROM events
WHERE JSON_CONTAINS(payload->'$.viewer_ids', '[42, 77]');

Typed extraction with JSON_VALUE

JSON_VALUE is the right tool when you want one scalar with a known SQL type: RETURNING removes the CAST, and ON EMPTY / ON ERROR let you decide per column whether a missing key is a default, a NULL, or a hard failure. Writing ON ERROR before ON EMPTY is deprecated and raises a warning, so keep them in that order.

Example · sql
SELECT
    id,
    -- Without RETURNING you get VARCHAR(512), so comparisons are textual
    -- and long values are cut short.
    JSON_VALUE(payload, '$.amount') AS amount_text,
    JSON_VALUE(payload, '$.amount'
               RETURNING DECIMAL(12,2)
               DEFAULT 0 ON EMPTY
               ERROR ON ERROR)      AS amount_num,
    JSON_VALUE(payload, '$.occurred_at' RETURNING DATETIME NULL ON ERROR) AS occurred_at
FROM events
WHERE type = 'payment';

Related

sql generated columnssql create indexsql data typessql castsql derived tables

Discussion

  • Be the first to comment on this lesson.