MySQL JSON Data Type

MySQL’s native JSON data type stores JSON documents and validates that inserted values are valid JSON. MySQL converts documents to an internal binary format that supports efficient access to nested values; the column is not stored as a plain text string.

Define a JSON column

CREATE TABLE events (
    event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    properties JSON NOT NULL
);

MySQL supports JSON columns starting with MySQL 5.7.8. A JSON column can contain an object, array, or scalar JSON value. Its maximum document size is subject to max_allowed_packet.

Default values

MySQL 8.0.13 and later allow an expression as a JSON column default. Write the expression in parentheses:

CREATE TABLE settings (
    id INT PRIMARY KEY,
    options JSON NOT NULL DEFAULT (JSON_OBJECT())
);

Before MySQL 8.0.13, a JSON column cannot have a non-NULL default value. See the official default-value rules for version and expression restrictions.

Index JSON values

MySQL does not create a normal index on an entire JSON document. To index a frequently queried scalar path, define a generated column that extracts the value and index that column:

CREATE TABLE customer_events (
    event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    properties JSON NOT NULL,
    event_type VARCHAR(32)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(properties, '$.type'))
        ) STORED,
    INDEX idx_customer_events_type (event_type)
);

InnoDB also supports multi-valued indexes for JSON arrays beginning with MySQL 8.0.17. Index design depends on the paths and predicates used by the queries; see the official JSON data type documentation and generated-column index documentation.

For step-by-step examples that insert JSON documents, extract values, and aggregate JSON data, see the MySQL JSON data type tutorial.