MySQL JSON поля: хранение и запросы к JSON-данным

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;