MySQL JSON поля

З MySQL 5.7 з'явився нативний тип даних JSON з валідацією, оптимізованим зберіганням та багатим набором функцій. Це зручно для зберігання конфігурацій, метаданих і напівструктурованих даних.

Створення таблиці з JSON полем

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

-- Вставка даних
INSERT INTO users (name, meta, settings) VALUES (
    'Alice',
    '{"age": 30, "tags": ["vip", "active"], "address": {"city": "Kyiv"}}',
    JSON_OBJECT('theme', 'dark', 'lang', 'uk')
);

Витягування даних

-- Оператор -> (повертає JSON)
SELECT meta->'$.age' AS age FROM users;

-- Оператор ->> (повертає текст, без лапок)
SELECT meta->>'$.address.city' AS city FROM users;

-- JSON_EXTRACT (те саме, що й ->)
SELECT JSON_EXTRACT(meta, '$.tags[0]') FROM users;

-- Фільтрація за JSON-значенням
SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

Оновлення JSON даних

-- Встановити значення
UPDATE users
SET meta = JSON_SET(meta, '$.age', 31, '$.verified', TRUE)
WHERE id = 1;

-- Додати до масиву
UPDATE users
SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'premium')
WHERE id = 1;

-- Видалити ключ
UPDATE users
SET meta = JSON_REMOVE(meta, '$.address')
WHERE id = 1;

Індексація JSON полів

-- MySQL не індексує JSON напряму.
-- Використовуйте віртуальний (generated) стовпець:
ALTER TABLE users
ADD COLUMN city VARCHAR(100)
GENERATED ALWAYS AS (meta->>'$.address.city') STORED;

CREATE INDEX idx_city ON users(city);

-- Тепер запит використовує індекс:
SELECT * FROM users WHERE city = 'Kyiv';

Корисні JSON функції

-- Перевірити валідність JSON
SELECT JSON_VALID('{"a":1}');  -- 1
SELECT JSON_VALID('bad json'); -- 0

-- Отримати тип значення
SELECT JSON_TYPE(meta->'$.tags'); -- ARRAY

-- Злиття об'єктів
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}'); -- {"a":1,"b":2}

-- Довжина масиву
SELECT JSON_LENGTH(meta->'$.tags') FROM users;

Не зловживайте JSON: Якщо поле часто бере участь у WHERE чи JOIN — винесіть його в звичайний стовпець з індексом. JSON зручний для гнучких метаданих, а не для реляційних зв'язків.

Типові помилки під час роботи з JSON

Помилка	Причина	Рішення
ERROR 3140 (22032): Invalid JSON text	У стовпець JSON намагаються вставити рядок, який не є валідним JSON (наприклад, одинарні лапки замість подвійних)	Перевірити рядок через `JSON_VALID()` перед вставкою на боці застосунку
ERROR 3143 (22030): Invalid JSON path expression	Помилка в шляху, наприклад `$.adress` замість `$.address`, або шлях починається не з `$`	Звірити шлях через `JSON_EXTRACT(meta, '$.address')` в інтерактивному режимі
ERROR 3168 (22018): JSON path expression does not contain any '[]' or '' wildcard	У функції, що очікує шлях з wildcard (наприклад, деяких агрегатних), вказано точний шлях без маски	Використати `$.tags[*]` замість `$.tags[0]` там, де потрібна вибірка за всіма елементами

Продуктивність: JSON проти окремих стовпців

Порівняння перевірено на таблиці з мільйона рядків: вибірка за звичайним індексованим стовпцем VARCHAR проходить за мілісекунди, а фільтрація за JSON-полем без generated column — це type: ALL в EXPLAIN, повний перебір таблиці, тому що MySQL не може побудувати індекс за довільним JSON-шляхом напряму.

Спосіб зберігання	Пошук за значенням	Зміна схеми
Звичайний стовпець + індекс	Швидкий, type=ref в EXPLAIN	Потребує ALTER TABLE
JSON без generated column	Full Table Scan, type=ALL	Не потребує ALTER TABLE
JSON + generated column + індекс	Швидкий, як звичайний стовпець	Потребує ALTER TABLE для додавання generated column

Коли JSON-поля не підходять

JSON у MySQL добре підходить для даних, структура яких змінюється від запису до запису і які рідко беруть участь у фільтрації чи JOIN — налаштування користувача, метадані інтеграцій, версійовані знімки. Якщо планується регулярно шукати, сортувати чи агрегувати за значенням усередині JSON, це сигнал, що поле має бути окремим реляційним стовпцем або окремою таблицею: JSON не замінює нормалізацію, а обходить її, і ціна обходу — або повний скан, або надлишкові generated columns на кожен потрібний шлях.

Перевірка плану запиту з JSON-фільтром

-- Без індексу — всюди ALL і високий rows
EXPLAIN SELECT * FROM users WHERE meta->>'$.address.city' = 'Kyiv';

-- Після додавання generated column та індексу
EXPLAIN SELECT * FROM users WHERE city = 'Kyiv';
-- очікуємо type=ref, rows помітно менше

-- Перевірити, який індекс реально використовується
SHOW INDEX FROM users WHERE Key_name = 'idx_city';

Про побудову звичайних індексів і правило порядку стовпців у складених індексах — у статті про індекси MySQL, а про читання виводу EXPLAIN у деталях — у статті про розбір плану запиту.