Что такое тип данных JSON в MySQL 5.7+ и как запрашивать JSON-поля?

Тип данных JSON в MySQL 5.7+

Начиная с MySQL 5.7 появился нативный тип JSON. В отличие от хранения в TEXT, MySQL валидирует JSON при вставке, хранит его в оптимизированном бинарном формате и предоставляет набор функций для извлечения, изменения и поиска значений. MySQL 8.0 расширил возможности: добавились multi-valued indexes, улучшилась поддержка оператора ->>, появились JSON_TABLE и JSON_SCHEMA_VALID.

Создание таблицы и вставка данных

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200),
  attributes JSON
) ENGINE=InnoDB;

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["sale", "new"], "specs": {"cpu": "i7", "gpu": "RTX4060"}}'),
  ('Phone',  '{"brand": "Samsung", "ram": 8, "tags": ["popular"], "specs": {"cpu": "Snapdragon"}}');

Извлечение значений

-- JSON_EXTRACT: возвращает значение с кавычками для строк
SELECT name, JSON_EXTRACT(attributes, '$.brand') AS brand
FROM products;
-- "Dell"  (с кавычками)

-- Оператор -> (псевдоним JSON_EXTRACT)
SELECT name, attributes->'$.brand' AS brand
FROM products;

-- Оператор ->> (JSON_UNQUOTE + JSON_EXTRACT): без кавычек
SELECT name, attributes->>'$.brand' AS brand
FROM products;
-- Dell  (без кавычек)

-- Вложенный путь
SELECT name, attributes->>'$.specs.cpu' AS cpu
FROM products;

-- Элемент массива по индексу
SELECT name, attributes->>'$.tags[0]' AS first_tag
FROM products;

Фильтрация по JSON-полям

-- Фильтр по значению ключа
SELECT * FROM products
WHERE attributes->>'$.brand' = 'Dell';

-- Числовое сравнение
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$.ram') >= 12;

-- Проверка наличия ключа
SELECT * FROM products
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.specs.gpu');

-- Поиск значения внутри массива
SELECT * FROM products
WHERE JSON_CONTAINS(attributes->'$.tags', '"sale"');
-- Обратите внимание: строку нужно оборачивать в кавычки внутри JSON

Изменение JSON-значений

-- Обновить конкретный ключ
UPDATE products
SET attributes = JSON_SET(attributes, '$.ram', 32)
WHERE id = 1;

-- Добавить новый ключ (JSON_INSERT не перезапишет существующий)
UPDATE products
SET attributes = JSON_INSERT(attributes, '$.warranty', 2)
WHERE id = 1;

-- Удалить ключ
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.tags[0]')
WHERE id = 1;

-- JSON_MERGE_PATCH (MySQL 8.0): обновить несколько полей сразу
UPDATE products
SET attributes = JSON_MERGE_PATCH(attributes, '{"ram": 64, "color": "black"}')
WHERE id = 1;

Индексирование JSON-полей

Прямой индекс на JSON-столбец создать нельзя. Есть два подхода:

-- 1. Generated (virtual) column + индекс (MySQL 5.7+)
ALTER TABLE products
  ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL,
  ADD INDEX idx_brand (brand);

-- Теперь запрос использует индекс:
EXPLAIN SELECT * FROM products WHERE brand = 'Dell';

-- 2. Multi-valued index для массивов (MySQL 8.0.17+)
ALTER TABLE products
  ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(50) ARRAY)));

-- Поиск по элементу массива через MEMBER OF
SELECT * FROM products
WHERE 'sale' MEMBER OF (attributes->'$.tags');

JSON_TABLE: разворачивание JSON в строки

-- MySQL 8.0: превратить массив тегов в отдельные строки
SELECT p.name, jt.tag
FROM products p,
     JSON_TABLE(
       p.attributes->'$.tags',
       '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
     ) AS jt;
-- Результат:
-- Laptop | sale
-- Laptop | new
-- Phone  | popular

Полезные утилитарные функции

-- Тип значения по пути
SELECT JSON_TYPE(attributes->'$.tags')  FROM products WHERE id = 1; -- ARRAY
SELECT JSON_TYPE(attributes->'$.ram')   FROM products WHERE id = 1; -- INTEGER

-- Длина массива
SELECT JSON_LENGTH(attributes->'$.tags') FROM products WHERE id = 1;

-- Все ключи первого уровня
SELECT JSON_KEYS(attributes) FROM products WHERE id = 1;
-- ["brand", "ram", "tags", "specs"]

-- Сформировать JSON из столбцов (JSON_OBJECT, JSON_ARRAY)
SELECT JSON_OBJECT('id', id, 'name', name) FROM products;

-- Агрегация в JSON-массив (MySQL 8.0)
SELECT JSON_ARRAYAGG(name) FROM products;
-- ["Laptop", "Phone"]

Подводные камни

• Оператор -> возвращает строки с кавычками ("Dell"), ->> — без (Dell). Сравнение attributes->'$.brand' = 'Dell' вернёт 0 из-за кавычек — всегда используйте ->> для строковых сравнений.
• JSON_CONTAINS для поиска строки в массиве требует передавать значение как JSON-строку: JSON_CONTAINS(col->'$.tags', '"sale"'), а не 'sale'.
• JSON-столбец нельзя индексировать напрямую — нужен generated column или multi-valued index. Без индекса любой фильтр по JSON — full table scan.
• JSON_SET перезапишет существующее значение, JSON_INSERT — нет. Путаница между ними приводит к потере данных или нежелательному сохранению старых значений.
• NULL внутри JSON ({"val": null}) и отсутствующий ключ — разные вещи: JSON_EXTRACT вернёт SQL NULL для отсутствующего ключа и JSON null (null) для явного null.
• Большие JSON-документы (>64 КБ) могут существенно замедлить запросы, так как MySQL читает весь документ из diска перед извлечением нужного поля.
• Репликация: при row-based binlog JSON-столбец хранится целиком для каждого UPDATE — даже если изменился один ключ, передаётся весь документ.
• JSON_MERGE устарела в MySQL 8.0 (deprecated), используйте JSON_MERGE_PATCH (заменяет ключи) или JSON_MERGE_PRESERVE (объединяет массивы).

• Не уточнять storage engine и говорить о json в mysql так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет json в mysql через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.