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.

Validate a JSON document against a schema

A JSON column validates JSON syntax, but it does not require a document to contain particular properties or values. In MySQL 8.0.17 and later, use JSON_SCHEMA_VALID() in a CHECK constraint for those rules:

CREATE TABLE schema_checked_events (
    event_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    properties JSON NOT NULL,
    CONSTRAINT chk_event_properties CHECK (
        JSON_SCHEMA_VALID(
            '{"type":"object","properties":{"event_type":{"type":"string"}},"required":["event_type"]}',
            properties
        )
    )
);

For the function’s Draft 4 limits and detailed failure reports, see JSON_SCHEMA_VALID() and JSON_SCHEMA_VALIDATION_REPORT(). If the same schema must work in MariaDB, compare the MySQL and MariaDB implementations before migrating it.

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.

If you have JSON exports or API responses that need to become relational rows, use the browser-based JSON to SQL converter to infer columns and generate MySQL CREATE TABLE and batched INSERT statements without uploading the source data.

When comparing database behavior, see the MariaDB JSON data type reference: MariaDB’s JSON alias uses text storage, while MySQL stores JSON in its binary format.