MySQL JSON Fields

MySQL 5.7 introduced a native JSON data type with validation, optimized storage and a rich set of functions. It is convenient for storing configurations, metadata and semi-structured data.

Creating a Table With a JSON Field

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    meta JSON,
    settings JSON
);

-- Inserting data
INSERT INTO users (name, meta, settings) VALUES (
    'Alice',
    '{"age": 30, "tags": ["vip", "active"], "address": {"city": "Kyiv"}}',
    JSON_OBJECT('theme', 'dark', 'lang', 'uk')
);

Extracting Data

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

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

-- JSON_EXTRACT (same as ->)
SELECT JSON_EXTRACT(meta, '$.tags[0]') FROM users;

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

Updating JSON Data

-- Set a value
UPDATE users
SET meta = JSON_SET(meta, '$.age', 31, '$.verified', TRUE)
WHERE id = 1;

-- Append to an array
UPDATE users
SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'premium')
WHERE id = 1;

-- Remove a key
UPDATE users
SET meta = JSON_REMOVE(meta, '$.address')
WHERE id = 1;

Indexing JSON Fields

-- MySQL does not index JSON directly.
-- Use a virtual (generated) column:
ALTER TABLE users
ADD COLUMN city VARCHAR(100)
GENERATED ALWAYS AS (meta->>'$.address.city') STORED;

CREATE INDEX idx_city ON users(city);

-- Now the query uses the index:
SELECT * FROM users WHERE city = 'Kyiv';

Useful JSON Functions

-- Check JSON validity
SELECT JSON_VALID('{"a":1}');  -- 1
SELECT JSON_VALID('bad json'); -- 0

-- Get the value type
SELECT JSON_TYPE(meta->'$.tags'); -- ARRAY

-- Merge objects
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}'); -- {"a":1,"b":2}

-- Array length
SELECT JSON_LENGTH(meta->'$.tags') FROM users;

Do not overuse JSON: If a field is often used in WHERE or JOIN, move it into a regular indexed column. JSON is convenient for flexible metadata, not for relational links.

Common Errors Working with JSON

Error	Cause	Fix
ERROR 3140 (22032): Invalid JSON text	An attempt to insert a string into a JSON column that is not valid JSON (for example, single quotes instead of double quotes)	Validate the string with `JSON_VALID()` before inserting it on the application side
ERROR 3143 (22030): Invalid JSON path expression	A typo in the path, such as `$.adress` instead of `$.address`, or the path does not start with `$`	Verify the path with `JSON_EXTRACT(meta, '$.address')` interactively
ERROR 3168 (22018): JSON path expression does not contain any '[]' or '' wildcard	A function that expects a path with a wildcard (for example, certain aggregate functions) was given an exact path without one	Use `$.tags[*]` instead of `$.tags[0]` where you need to match every element

Performance: JSON vs Separate Columns

Tested on a table of one million rows: a lookup on a regular indexed VARCHAR column takes milliseconds, while filtering on a JSON field without a generated column shows type: ALL in EXPLAIN — a full table scan, because MySQL cannot build an index on an arbitrary JSON path directly.

Storage Approach	Lookup by Value	Schema Change
Regular column + index	Fast, type=ref in EXPLAIN	Requires ALTER TABLE
JSON without generated column	Full Table Scan, type=ALL	No ALTER TABLE needed
JSON + generated column + index	Fast, like a regular column	Requires ALTER TABLE to add the generated column

When JSON Fields Are Not a Good Fit

JSON in MySQL works well for data whose structure varies from row to row and which is rarely used in filtering or JOIN — user preferences, integration metadata, versioned snapshots. If you plan to regularly search, sort, or aggregate by a value inside the JSON, that is a sign the field should be a separate relational column or table: JSON does not replace normalization, it works around it, and the cost of working around it is either a full scan or redundant generated columns for every path you need.

Checking the Query Plan with a JSON Filter

-- Without an index — ALL everywhere and high rows
EXPLAIN SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

-- After adding a generated column and an index
EXPLAIN SELECT * FROM users WHERE city = 'Kyiv';
-- expect type=ref, rows noticeably lower

-- Check which index is actually being used
SHOW INDEX FROM users WHERE Key_name = 'idx_city';

Building regular indexes and the column-order rule for composite indexes are covered in the article on MySQL indexes, and reading EXPLAIN output in detail is covered in the article on reading a query plan.