Home/Learn/MySQL/JSON Data Type & Functions

JSON Data Type & Functions

Intermediate

Schema Design

MySQL 5.7+ stores JSON as a binary type with path expressions (->>, JSON_EXTRACT, JSON_SET); generated virtual columns on JSON paths can be indexed for efficient JSON queries.

Overview

MySQL 5.7+ stores JSON as a binary-encoded type (not plain TEXT) that validates structure on insert and enables efficient partial updates and path-based extraction. JSON is suitable for semi-structured attributes that vary per row — product metadata, configuration, user preferences — where adding a nullable column per attribute would create a wide sparse table. The -> operator is shorthand for JSON_EXTRACT; ->> additionally unquotes the result. JSON documents can be indexed by creating generated virtual columns on specific JSON paths and adding a regular index to the virtual column. MySQL 8.0 adds JSON_TABLE() to pivot JSON arrays into relational rows, and supports multi-value indexes on JSON arrays.

Storing, reading, and updating JSON

Insert JSON with a JSON string literal or MySQL's JSON construction functions. Read specific paths with ->/->> operators or JSON_EXTRACT. Modify individual paths with JSON_SET/JSON_REPLACE/JSON_REMOVE without rewriting the whole document — MySQL stores JSON in binary format that supports efficient in-place partial updates (JSON_MERGE_PATCH).

SQL — JSON insert, path extraction, and partial updates

-- Create table with JSON column
CREATE TABLE products (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(200),
    attributes JSON NOT NULL   -- validated on insert
);

-- Insert JSON
INSERT INTO products (name, attributes)
VALUES ('Widget Pro', '{"color":"blue","weight":0.5,"tags":["sale","new"]}');

-- Read with -> (returns quoted string) and ->> (unquoted)
SELECT
    attributes->'$.color'        AS color_quoted,    -- "blue"
    attributes->>'$.color'       AS color_plain,     -- blue
    attributes->'$.tags[0]'      AS first_tag,       -- "sale"
    JSON_EXTRACT(attributes, '$.weight') AS weight;  -- 0.5

-- Update single path (does not rewrite whole document)
UPDATE products
SET attributes = JSON_SET(attributes,
    '$.color', 'red',
    '$.price', 9.99)
WHERE id = 1;

-- Remove a key
UPDATE products SET attributes = JSON_REMOVE(attributes, '$.tags') WHERE id = 1;

Indexing JSON with generated virtual columns

MySQL cannot index a JSON column directly, but you can create a virtual generated column that extracts a specific path and index that column. The virtual column does not store data on disk — it is computed on read. This enables B-tree index lookups on JSON paths with zero storage overhead.

SQL — virtual generated column index and multi-value JSON array index

-- Add virtual generated column + index for a frequently queried JSON path
ALTER TABLE products
    ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.color') VIRTUAL,
    ADD INDEX idx_color (color);

-- Query now uses idx_color (same syntax as before — MySQL rewrites it)
SELECT * FROM products WHERE attributes->>'$.color' = 'blue';
-- EXPLAIN shows: key = idx_color (not full scan)

-- Multi-value index on JSON array (MySQL 8.0.17+)
ALTER TABLE products
    ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(50) ARRAY)));

-- Query using MEMBER OF() to use multi-value index
SELECT * FROM products
WHERE 'sale' MEMBER OF(attributes->'$.tags');
-- Uses multi-value index — efficient for JSON array containment checks

JSON_TABLE — pivoting JSON to relational rows

JSON_TABLE() (MySQL 8.0+) converts JSON arrays into relational rows in a FROM clause. Useful for normalising semi-structured data inline, building reports from JSON event logs, or unnesting embedded arrays without application-level processing.

SQL — JSON_TABLE for JSON array unnesting and JSON_ARRAYAGG for aggregation

-- Sample: orders with JSON line-items array
SELECT o.id, o.customer_id, item.*
FROM orders o,
JSON_TABLE(
    o.line_items,
    '$[*]' COLUMNS (
        product_id  INT          PATH '$.product_id',
        qty         INT          PATH '$.qty',
        unit_price  DECIMAL(8,2) PATH '$.price',
        product_name VARCHAR(200) PATH '$.name'
            ERROR ON ERROR DEFAULT 'Unknown' ON EMPTY
    )
) AS item
WHERE o.customer_id = 42;

-- Result: one row per line item, joined with order columns
-- order_id | customer_id | product_id | qty | unit_price | product_name
--        1 |          42 |        101 |   2 |       9.99 | Widget

-- JSON aggregation in reverse — aggregate rows to JSON
SELECT customer_id,
       JSON_ARRAYAGG(
           JSON_OBJECT('product_id', product_id, 'total', SUM(qty * price))
       ) AS order_summary
FROM order_items
GROUP BY customer_id;

Key Points to Remember

1MySQL JSON columns store binary-encoded JSON — validated on insert, supports partial path-based updates without full rewrite
2-> returns a quoted JSON value; ->> returns the unquoted scalar string — use ->> for string comparisons and WHERE clauses
3JSON columns cannot be directly indexed — create a virtual generated column on a path and index the virtual column
4Multi-value indexes (MySQL 8.0.17+) enable efficient MEMBER OF() queries on JSON arrays
5JSON_SET/JSON_REMOVE modify individual paths; JSON_MERGE_PATCH merges/overwrites a document with a patch object
6JSON_TABLE() (MySQL 8.0) pivots JSON arrays to relational rows in a FROM clause — useful for reports and unnesting

Interview Questions

What is the difference between the -> and ->> operators in MySQL JSON queries?

EasyOracle

How would you create an index on a specific JSON path in MySQL?

MediumThoughtworks

When is a JSON column a better choice than additional nullable columns in a table?

MediumFlipkart

How do multi-value indexes work and what query pattern do they optimise?

HardAmazon

How would you use JSON_TABLE to query an orders table where line items are stored as a JSON array?

HardBooking.com

Ask Aria about JSON Data Type & Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Table Partitioning

Replication (Primary-Replica)