Работа с JSON в SQL
Хранение, извлечение, построение, проверка и индексирование полуструктурированных данных внутри реляционной базы
JSON позволяет одной ячейке хранить целый документ — вложенные объекты и массивы — вместо плоского скалярного значения. MySQL разбирает и проверяет этот документ при записи, а затем даёт вам семейство функций и операторы -> / ->>, чтобы добираться внутрь него во время запроса. Это мощно для разреженных, изменчивых или развивающихся атрибутов, но это не разрешение отказаться от нормализации: каждый выбор дизайна обменивает гибкость схемы на производительность запросов и целостность данных.
- объявить столбец
JSONи понять, как MySQL хранит и проверяет его, - извлекать значения с помощью
JSON_EXTRACT, оператора->и убирающего кавычки оператора->>, - читать JSON-путь, например
$.color,$.tags[0]или$.dims.w, - строить JSON с помощью
JSON_OBJECT,JSON_ARRAYи агрегировать черезJSON_ARRAYAGG/JSON_OBJECTAGG, - изменять документы с помощью
JSON_SET,JSON_REMOVEи подобных, - проверять через
JSON_VALID/JSON_TYPEи индексировать JSON через сгенерированные столбцы и многозначные индексы, - честно решать, когда настоящая реляционная таблица лучше JSON-блоба.
1. Наша примерная схема
Всё далее выполняется над одной крошечной базой магазина. У каждого товара есть несколько фиксированных столбцов и столбец JSON под названием attributes, который несёт всё, что различается у товаров: цвет, размер, массив тегов, вложенные размеры. Телефон и футболка не имеют одинаковых характеристик, а придумывать широкую таблицу с десятками почти пустых столбцов было бы расточительно — это классический случай для JSON.
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL,
attributes JSON
);
INSERT INTO products (name, price, attributes) VALUES
('Cotton Tee', 19.99,
'{"color":"red","size":"M","tags":["sale","cotton"]}'),
('Running Shoe', 89.50,
'{"color":"black","size":42,"tags":["sport"],"dims":{"w":10,"h":30}}'),
('Ceramic Mug', 12.00,
'{"color":"white","tags":[]}');Обратите внимание: у второй строки есть вложенный объект dims и числовой size, тогда как у кружки size отсутствует вовсе, а массив tags пуст. JSON терпит эту вариативность по строкам; фиксированный набор столбцов — нет.