SQL JSON: практическое применение

Для чего еще используется JSON в индустрии?

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

Рассмотрим пример таблицы с логами событий:

CREATE TABLE site_logs (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO site_logs (event_name, event_data)
VALUES 
    ('user_login', '{"user_id": 123, "browser": "Chrome", "ip": "192.168.1.1"}'),
    ('product_view', '{"product_id": 456, "category": "electronics", "user_id": 123}'),
    ('cart_add', '{"product_id": 456, "quantity": 2, "user_id": 123, "discount_applied": true}');

Мы получим вот такую таблицу:

id event_name event_data created_at
1 user_login {"user_id": 123, "browser": "Chrome", "ip": "192.168.1.1"} 2024-01-01 12:00:00
2 product_view {"product_id": 456, "category": "electronics", "user_id": 123} 2024-01-01 12:05:00
3 cart_add {"product_id": 456, "quantity": 2, "user_id": 123, "discount_applied": true} 2024-01-01 12:10:00

В этом примере каждый тип события (user_login, product_view, cart_add) имеет свои уникальные параметры, которые хранятся в поле event_data в формате JSON. Это позволяет гибко добавлять новые параметры без изменения структуры таблицы.

Пример запроса для извлечения данных из JSON:

SELECT event_name, event_data->>'user_id' AS user_id
FROM site_logs
WHERE event_data @> '{"user_id": 123}';
event_name user_id
user_login 123
product_view 123
cart_add 123

Этот запрос извлекает все события, связанные с пользователем с user_id = 123, и показывает название события и идентификатор пользователя.