Работа с данными JSON в MySQL

Введение

В этой лабораторной работе вы научитесь эффективно использовать тип данных JSON в MySQL. Вы выполните основные операции, такие как вставка JSON-документов, запрос конкретных полей с использованием функций, таких как JSON_EXTRACT и оператора ->>, изменение данных в столбце JSON, а также оптимизация запросов путем создания индекса по свойству JSON.

В ходе этой лабораторной работы вы подключитесь к серверу MySQL, создадите выделенную базу данных и таблицу, а затем выполните серию практических задач для развития навыков управления данными JSON в контексте реляционной базы данных.

Подключение к MySQL и создание базы данных

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

Сначала откройте терминал на вашем рабочем столе.

Подключитесь к серверу MySQL с привилегиями пользователя root. В этой лабораторной среде sudo позволяет подключаться без пароля.

sudo mysql -u root

После подключения командная строка изменится на mysql>, что означает, что вы находитесь в оболочке MySQL.

Далее создайте новую базу данных с именем jsondb. Предложение IF NOT EXISTS гарантирует, что команда выполнится без ошибок, если база данных уже существует.

CREATE DATABASE IF NOT EXISTS jsondb;

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

USE jsondb;

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

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    product_details JSON
);

Это утверждение определяет таблицу с тремя столбцами:

id: Уникальное целое число с автоинкрементом для каждой записи.
product_name: Строка для названия продукта.
product_details: Столбец JSON для хранения структурированных данных.

Вы успешно настроили необходимую базу данных и таблицу. Оставьте оболочку MySQL открытой для следующего шага.

Вставка и запрос данных JSON

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

В той же оболочке MySQL выполните следующую команду INSERT для добавления нового продукта.

INSERT INTO products (product_name, product_details) VALUES (
    'Laptop',
    '{
        "brand": "Dell",
        "model": "XPS 13",
        "specs": {
            "processor": "Intel Core i7",
            "memory": "16GB",
            "storage": "512GB SSD"
        },
        "price": 1200
    }'
);

Эта команда вставляет запись 'Laptop'. Столбец product_details заполняется JSON-объектом, который включает вложенные данные, такие как specs.

Чтобы убедиться, что данные были вставлены правильно, выполните запрос к таблице products, чтобы просмотреть ее содержимое.

SELECT * FROM products;

Вывод должен отобразить только что вставленную строку. Обратите внимание, как JSON-данные хранятся в столбце product_details.

+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | product_name | product_details                                                                                                                |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|  1 | Laptop       | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Вы успешно вставили запись с данными JSON. На следующем шаге вы научитесь извлекать конкретную информацию из этого JSON-объекта.

Извлечение данных из полей JSON

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

Функция JSON_EXTRACT позволяет выбрать значение из JSON-документа, используя выражение пути. Путь начинается с $ для представления корневого элемента документа.

Извлечем brand ноутбука.

SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';

Этот запрос возвращает бренд, но обратите внимание, что результат представляет собой JSON-строку, включающую двойные кавычки.

+--------+
| brand  |
+--------+
| "Dell" |
+--------+
1 row in set (0.00 sec)

Для получения более чистого результата вы можете использовать JSON_UNQUOTE в сочетании с JSON_EXTRACT. Эта комбинация извлекает значение и удаляет кавычки, возвращая стандартную строку.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';

Теперь вывод представляет собой обычный текст Dell.

+-------+
| brand |
+-------+
| Dell  |
+-------+
1 row in set (0.00 sec)

Вы также можете использовать выражения пути для доступа к значениям во вложенных объектах. Чтобы получить processor из объекта specs, используйте путь $.specs.processor.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';

Это правильно извлечет вложенное значение.

+-----------------+
| processor       |
+-----------------+
| Intel Core i7   |
+-----------------+
1 row in set (0.00 sec)

Эти функции также полезны в предложениях WHERE для фильтрации строк. Чтобы найти все продукты с ценой выше 1000, вам необходимо CAST (преобразовать) извлеченное значение JSON в числовой тип для сравнения.

SELECT product_name, JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1000;

Этот запрос демонстрирует, как фильтровать записи на основе числового значения внутри поля JSON.

+--------------+-------+
| product_name | price |
+--------------+-------+
| Laptop       | 1200  |
+--------------+-------+
1 row in set (0.00 sec)

Теперь вы знаете, как извлекать и фильтровать данные на основе полей JSON.

Обновление и добавление полей JSON

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

Функция JSON_SET изменяет JSON-документ, принимая в качестве аргументов целевой столбец, путь к полю и новое значение.

Сначала обновим price ноутбука с 1200 до 1250.

UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';

Чтобы проверить изменение, снова запросите цену.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';

Вывод теперь должен показать новую цену.

+-------+
| price |
+-------+
| 1250  |
+-------+
1 row in set (0.00 sec)

Если указанный путь не существует, JSON_SET добавит новый ключ и значение. Добавим свойство color к продукту.

UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';

Теперь запросите весь JSON-объект, чтобы увидеть недавно добавленное поле.

SELECT product_details FROM products WHERE product_name = 'Laptop';

Вывод покажет документ product_details, который теперь включает свойство color.

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| product_details                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Вы успешно изменили и расширили JSON-документ в таблице.

Создание индекса по свойству JSON

Для больших таблиц запросы к полям JSON могут быть медленными. Для повышения производительности вы можете создать индекс по значению, извлеченному из столбца JSON. В MariaDB это достигается путем добавления виртуального столбца на основе поля JSON, а затем создания индекса на этом виртуальном столбце.

На этом этапе вы создадите виртуальный столбец для свойства price и затем проиндексируете его для ускорения запросов, основанных на цене.

Сначала добавьте виртуальный столбец, который извлекает цену из данных JSON:

ALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;

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

Теперь создайте индекс на этом виртуальном столбце:

CREATE INDEX idx_product_price ON products (price_virtual);

Этот подход позволяет MariaDB эффективно искать строки по числовой цене, используя индексированный виртуальный столбец.

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

SHOW INDEXES FROM products;

Вывод перечислит все индексы в таблице products, включая ваш новый idx_product_price.

+----------+------------+-------------------+...
| Table    | Non_unique | Key_name          |...
+----------+------------+-------------------+...
| products |          0 | PRIMARY           |...
| products |          1 | idx_product_price |...
+----------+------------+-------------------+...

Самое важное — увидеть, использует ли оптимизатор индекс. Вы можете проверить это с помощью команды EXPLAIN.

EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200;

В выводе EXPLAIN посмотрите на столбцы possible_keys и key. Вы должны увидеть там idx_product_price, что подтверждает, что MariaDB использует ваш индекс для эффективного выполнения запроса.

Вы также можете выполнять запросы, используя исходное выражение JSON, и оптимизатор MariaDB все равно сможет использовать индекс на виртуальном столбце:

EXPLAIN SELECT product_name FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1200;

Вы успешно создали виртуальный столбец и проиндексировали его для оптимизации запросов к свойствам JSON.

Теперь вы можете выйти из оболочки MySQL.

exit

Резюме

В этой лабораторной работе вы получили практический опыт работы с данными JSON в MariaDB. Вы изучили полный рабочий процесс, от настройки структуры базы данных до выполнения расширенных операций.

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

Эти навыки ценны для проектирования гибких схем баз данных и эффективного управления полуструктурированными данными в MariaDB.