JSON в SQL: работа с JSON-типами в PostgreSQL, MySQL и других СУБД
Зачем JSON в SQL? 🤔
Когда реляционные базы встречаются с полуструктурированными данными — рождается магия! JSON в SQL позволяет:
- Хранить вложенные структуры (например, профиль пользователя с адресом и метаданными)
- Работать с API-ответами прямо в БД
- Гибко менять схемы без ALTER TABLE
-- Пример: интернет-магазин хранит атрибуты товара в JSON
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes JSONB -- PostgreSQL
);
Поддержка JSON в популярных СУБД ️
PostgreSQL: JSON и JSONB
JSONB — бинарный формат (быстрее поиск, медленнее запись). Поддержка операторов:
-- Вставка
INSERT INTO products (name, attributes)
VALUES ('Книга', '{"author": "Достоевский", "pages": 300, "tags": ["классика", "литература"]}');
-- Поиск по ключу
SELECT name FROM products WHERE attributes->>'author' = 'Достоевский';
-- Обновление поля
UPDATE products
SET attributes = jsonb_set(attributes, '{pages}', '350')
WHERE id = 1;
MySQL: JSON с проверкой валидности
-- Извлечение данных
SELECT name, JSON_EXTRACT(attributes, '$.author')
FROM products;
-- Специальный синтаксис ->>
SELECT name FROM products WHERE attributes->>"$.author" = 'Достоевский';
SQLite: JSON как текст (+ расширения)
-- Активация JSON1
SELECT json_extract('{"a": 2}', '$.a'); -- Вернет 2
Крутые фишки работы с JSON
1. Преобразование строк в JSON
PostgreSQL:
SELECT json_build_object('name', name, 'price', price)
FROM products;
MySQL:
SELECT JSON_OBJECT('name', name, 'price', price)
FROM products;
2. Агрегация в JSON-массив
-- Собираем все теги товаров в один массив (PostgreSQL)
SELECT jsonb_agg(attributes->'tags')
FROM products;
3. Развертывание JSON в таблицу
-- PostgreSQL: jsonb_to_recordset
SELECT *
FROM jsonb_to_recordset(
'[{"name": "Чайник", "price": 2000}, {"name": "Лампа", "price": 1500}]'
) AS x(name text, price int);
Когда использовать JSON, а когда нет? ️
✅ Хорошо:
- Динамические атрибуты (настройки, характеристики товаров)
- Временные данные (кеш API-ответов)
- Сложные иерархии (деревья комментариев)
❌ Плохо:
- Данные, участвующие в JOIN (используйте нормальные таблицы)
- Частые обновления внутренних полей
- Критичные к производительности запросы
Практический кейс: API-шлюз в БД 🚀
Представим, что мы кэшируем ответы внешнего API:
CREATE TABLE api_cache (
endpoint VARCHAR(255) PRIMARY KEY,
response JSONB,
updated_at TIMESTAMP
);
-- Обновляем кэш
UPDATE api_cache
SET
response = '{"weather": {"temp": 15, "status": "rainy"}}',
updated_at = NOW()
WHERE endpoint = '/weather/moscow';
Запрос с извлечением данных:
SELECT
response->'weather'->>'status' AS weather_status,
updated_at
FROM api_cache
WHERE endpoint = '/weather/moscow';
Производительность: индексы на JSON 📊
PostgreSQL:
-- Создаем GIN-индекс для быстрого поиска
CREATE INDEX idx_products_tags ON products USING GIN ((attributes->'tags'));
MySQL:
-- Виртуальная колонка + индекс
ALTER TABLE products
ADD COLUMN author VARCHAR(100)
GENERATED ALWAYS AS (attributes->>"$.author") STORED;
CREATE INDEX idx_author ON products(author);
Ошибки новичков (и как их избежать) 🚧
1. Чрезмерное вложение:
/* Плохо */
{"user": {"profile": {"contacts": {"email": "..."}}}}
2. Отсутствие валидации — используйте CHECK в PostgreSQL:
ALTER TABLE products
ADD CHECK (jsonb_typeof(attributes->'price') = 'number');
3. Игнорирование NULL — всегда обрабатывайте:
SELECT COALESCE(attributes->>'discount', '0') FROM products;
Экспериментируйте! 🔥
Попробуйте прямо сейчас:
-- PostgreSQL playground
SELECT
jsonb_pretty(
jsonb_build_object(
'current_time', NOW(),
'random_number', FLOOR(RANDOM() * 100)
)
);
