MySQL JSON Fields: Storing and Querying JSON Data

MySQL JSON Fields

MySQL 5.7+ introduced a native JSON data type with validation and optimized storage. Ideal for configurations, metadata, and semi-structured data.

Querying JSON

-- -> operator (returns JSON)
SELECT meta->'$.age' AS age FROM users;

-- ->> operator (returns text)
SELECT meta->>'$.address.city' AS city FROM users;

-- Filter by JSON value
SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

JSON Indexing

-- Use a generated column:
ALTER TABLE users
ADD COLUMN city VARCHAR(100)
GENERATED ALWAYS AS (meta->>'$.address.city') STORED;

CREATE INDEX idx_city ON users(city);