Работа с JSON данными в MySQL: выборка и нормализация

Быстрый ответ

Для извлечения данных JSON из таблицы MySQL применяется функция JSON_EXTRACT():

SELECT JSON_EXTRACT(user_profile, '$.interests') AS interests
FROM users;

Укажите путь '$.interests' для обращения к нужному элементу в поле user_profile. Задайте необходимый путь для доступа к другим частям JSON.

Чеклист перед началом работы

Нормализация базы данных

Прежде чем проводить загрузку данных в столбец JSON, обдумайте, действительно ли это необходимо? Может быть, целесообразней нормализовать базу данных, разделив данные на отдельные таблицы. Этот принцип похож на разделение ингредиентов при выпечке: желтки идут в состав торта (структурированные данные), а белки используются для безе (неструктурированные JSON данные).

Обработка строкового формата JSON

Если вы столкнулись с JSON, начинающимся с двойных кавычек, значит, JSON представлен в формате строки. Для доступа к данным такого формата примените функцию JSON_UNQUOTE().

Версия MySQL

Убедитесь, что ваша версия MySQL поддерживает работу с JSON. С версии 5.7 возможность работы с JSON значительно упрощена. Если у вас более старая версия, рассмотрите вариант её обновления.

Работа с JSON в MySQL

Просмотр данных: Представления MySQL

Для упрощения взаимодействия с JSON рекомендуется создать представления MySQL:

CREATE VIEW user_interests AS
SELECT 
  JSON_EXTRACT(user_profile, '$.interests') AS interests
FROM users;

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

Скорость важна: Производительность

Функция JSON_EXTRACT() может быть недостаточно быстрой при работе с объемными и сложными JSON данными. С целью повышения производительности в критических ситуациях разбивайте JSON на реляционные таблицы или используйте материализованные представления.

Визуализация

Представим, что требуется извлечь некоторые данные из JSON-столбца в MySQL таблице:

Таблица 🏍️: `riders`
Столбец 📝: `info` (тип данных JSON)

Для извлечения имени гонщика и модели его мотоцикла, составьте следующий запрос:

SELECT `info` -> '$.name' AS 'Name', 
       `info` -> '$.motorcycle.model' AS 'Motorcycle Model'
FROM `riders`;

В данном случае оператор -> выступает в роли инструмента, "достающего" необходимые данные из "ткани" JSON.

Полезные материалы

1. Официальная документация MySQL 5.7 — все, что необходимо знать о работе с JSON в MySQL.
2. Статья на портале SitePoint — подробное руководство о работе с JSON-данными в базах данных MySQL.
3. DB Fiddle — площадка для тестирования SQL-запросов.
4. Блог Percona — советы по ускорению доступа к JSON данным в MySQL.
5. Статья на Medium — об операциях CRUD с JSON в MySQL, направленная на разработчиков.