MySQL JSON Data Type: Store and Query JSON

MySQL’s native JSON data type validates documents and stores them in an optimized binary format. Use it when a record needs flexible properties, while keeping frequently filtered or joined fields in regular columns.

Create a table with JSON columns

This example stores page-view and purchase events. properties holds event-specific data, and browser holds browser details:

CREATE TABLE events (
    event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(32) NOT NULL,
    visitor_id INT NOT NULL,
    properties JSON NOT NULL,
    browser JSON NOT NULL
);

Insert JSON objects with JSON_OBJECT() so MySQL constructs valid JSON values:

INSERT INTO events (event_type, visitor_id, properties, browser)
VALUES
    ('page_view', 1, JSON_OBJECT('page', '/'),
        JSON_OBJECT('name', 'Safari', 'os', 'macOS')),
    ('page_view', 2, JSON_OBJECT('page', '/contact'),
        JSON_OBJECT('name', 'Firefox', 'os', 'Windows')),
    ('purchase', 3, JSON_OBJECT('amount', 200.00),
        JSON_OBJECT('name', 'Firefox', 'os', 'Windows')),
    ('purchase', 3, JSON_OBJECT('amount', 150.00),
        JSON_OBJECT('name', 'Chrome', 'os', 'Windows'));

The JSON column rejects malformed JSON documents. A JSON column can have an expression default in MySQL 8.0.13 and later; see the JSON data type reference for the version and syntax details.

Extract JSON values

Use -> to extract a JSON value. A JSON string remains quoted in the JSON result:

SELECT event_id, browser->'$.name' AS browser_json
FROM events;

Use ->> when you need the unquoted scalar text, such as a value to display or compare:

SELECT event_id, browser->>'$.name' AS browser_name
FROM events;

The operators are shorthand for JSON_EXTRACT() and JSON_UNQUOTE(JSON_EXTRACT()), respectively. The path $.name selects the top-level name property. For nested objects, use a path such as $.resolution.width.

Filter rows by a JSON property with WHERE:

SELECT event_id, visitor_id
FROM events
WHERE properties->>'$.page' = '/contact';

Aggregate values from JSON

->> returns text, so cast numeric JSON values before arithmetic or aggregation:

SELECT
    visitor_id,
    SUM(CAST(properties->>'$.amount' AS DECIMAL(10,2))) AS revenue
FROM events
WHERE event_type = 'purchase'
GROUP BY visitor_id;

Index a frequently queried JSON path

MySQL does not create a regular index on the entire JSON document. For a scalar path that appears often in filters, add a generated column and index it:

ALTER TABLE events
    ADD COLUMN page_path VARCHAR(255)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(properties, '$.page'))
        ) STORED,
    ADD INDEX idx_events_page_path (page_path);

Then filter on the generated column:

SELECT event_id, visitor_id
FROM events
WHERE page_path = '/contact';

For JSON arrays, InnoDB also supports multi-valued indexes beginning with MySQL 8.0.17. Index only paths and array values your queries need; see the MySQL JSON reference for indexing details.

MySQL’s official JSON documentation covers validation, JSON paths, partial updates, and indexing restrictions.