Использование JSON в MySQL

Если вы работали с SQL базами данных, то наверняка согласитесь что проектировать структуру кажется проще, чем есть на самом деле. SQL базы данных верят в структуру, именно поэтому и называются Structured Query Language.

С другой стороны сегодня не менее популярные NoSQL базы данных, которые также называются schema-less - они жестко не привязаны к заданной структуре и не имеют структурных ограничений.

Конечно каждый инструмент имеет свою область применения, иногда требуются комбинированные решения. Что если вы можете жестко структурировать некоторые части приложения, а некоторые сделать гибкими? MySQL в версии 5.7.8 представил новый тип данных - JSON, которые позволяет решить эту проблему.

Зачем использовать JSON

На данный момент вы наверняка спрашиваете себя зачем нужно использовать JSON, когда MySQL итак неплохо справляется со своими функциями.

Давайте рассмотрим на примере. Допустим вы создаете веб-приложение где вам нужно сохранять пользовательские настройки в базе данных. Как правило вы можете создать отдельную страницу с полями id, user_id и values или сохранять их в некую форматированную строку и парсить в рантайме. Это неплохо работает если у вас немного пользователей. Если же у вас тысяча пользователей и пять конфигурационных ключей, то это уже пять тысяч записей, которые тем не менее составляют лишь малую часть вашего приложения. Разбор форматированной строки в рантайме лишь создает дополнительную нагрузку. Использование JSON в данном случае может сэкономить ваши ресурсы, уменьшить количество записей в базе и количество дополнительных запросов.

Структура

Перед тем как мы погрузимся в изучение JSON функционала, нам нужен пример базы для работы. Так что сначала набросаем структуру. Мы будем рассматривать случай использования интернет-магазина, где находится несколько брендов и множество электроники. Поскольку различные гаджеты имеют различные атрибуты (сравните Macbook с пылесосом), как правило используется модель Entity-Attribute-Value (EAV) модель. Но мы откажемся от нее, так как будем использовать JSON.

Наша база данных будет называться store и иметь три таблицы - brands, categories и products. Таблицы brands и categories будут очень похожи и иметь только поля id и name.

CREATE DATABASE IF NOT EXISTS `store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

Цель этих двух таблиц - хранение категорий и брэндов наших продуктов. Добавим немного данных.

/* Brands */
INSERT INTO `store`.`brands`(`name`) VALUES
    ('Samsung'), ('Nokia'), ('Canon');

/* Types of electronic device */
INSERT INTO `store`.`categories`(`name`)
VALUES ('Television'), ('Mobile phone'), ('Camera');

Теперь создадим таблицу products с полями id,name,brand_id,category_idиattributes`.

CREATE TABLE `store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

Наша структура определяет два внешних ключа для полей brand_id и category_id, ссылающихся на таблицы brands и categories соответственно.

Поле attributes было определено как JSON, что позволит нам использовать доступный в MySQL JSON-функционал для этого поля.

Вот такая структура базы данных у нас получилась в итоге.

Mysql json

CRUD операции

Давайте посмотрим как мы можем создавать, читать, обновлять и удалять данные в JSON полях.

Создание

Создание записи в таблице с JSON полем достаточно простое. Все что нужно - это добавить валидный JSON в значение этого поля в insert запросе.

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

Также вместо того, чтобы самому кодировать JSON объект вы можете использовать встроенную функцию JSON_OBJECT. Эта функция принимает список пар ключ-значение вида JSON_OBJECT(key1, value1, key2, value2, … key(n), value(n)) и возвращает JSON объект.

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

Стоит обратить внимание на функцию JSON_ARRAY, которая возвращает массив JSON объектов.

Если вы укажете один ключ несколько раз - только первая пара ключ-значение будет использована. Также ключи объектов сортируются и очищаются от пробелов в начале.

Еще одна функция для работы с JSON - JSON_MERGE. Функция принимает несколько JSON объектов и возвращает один объединенный.

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

В JSON_MERGE мы передаем только объекты. Некоторые из них были созданы с использованием JSON_OBJECT, которую вы разбирали ранее. Если в JSON_MERGE ключ повторяется несколько раз, то его значения будут объединены в массив.

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

Мы можем проверить результат с использование функции JSON_TYPE, которая возвращает тип хранимого значения.

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `store`.`products`;

Чтение

Итак, мы имеем несколько продуктов в нашей базе.

Для обычных MySQL запросов условие WHERE выглядит довольно просто - нужно указать поле, оператор и значение. Для JSON полей это не будет работать.

SELECT
    *
FROM
    `store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"

Для выбора нужных строк по JSON полю нужно быть знакомым с понятием пути. Самые простые примеры пути - css селекторы. Вторая часть пазла - функция JSON_EXTRACT, которая принимает в качестве аргумента путь для перемещения по JSON объекту.

К примеру мы интересуемся в телевизорах с USD или HDMI портом.

SELECT
    *
FROM
    `store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

Обновление

Для обновления JSON значений нужно использовать JSON_INSERT, JSON_REPLACE и JSON_SET функции. Эти функции также требуют путь чтобы обозначить какие част JSON объекта нужно изменить. Вывод этих функций - валидный JSON объект с примененными изменениями.

Давайте изменим все мобильные телефоны и добавим свойство чипсета.

UPDATE `store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

Путь $.chipset определяет расположение свойства chipset в корне объекта.

Давайте обновим свойство chipset с помощью JSON_REPLACE.

UPDATE `store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

Напоследок у нас осталась функция JSON_SET и мы используем ее для обновления телевизоров.

UPDATE `store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

Эти функции кажутся идентичными, но есть разница в их поведении. JSON_INSERT добавляет свойство к объекту только если оно еще не существует. JSON_REPLACE заменяет свойство только если оно существует. JSON_SET добавляет свойство или заменяет его если оно уже существует.

Удаление

Здесь мы рассмотрим два случая удаления данных.

Первый - удаление конкретного ключа-значения из ваших JSON полей, второй - удаление строк целиком с условием по JSON полям.

К примеру мы больше не предоставляем mount_type информации для камер и хотим удалить ее для всех камер. Мы сделаем это с использованием JSON_REMOVE, которая возвращает обновленный JSON без указанного в пути ключа.

UPDATE `store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

Для второго случая, мы удалим все телефоны с версией Android OS Jellybean.

DELETE FROM `store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

Другие публикации