re:re:actable
Урок 064 минуты чтения

Когда использовать JSON в SQL базе

На этой странице
Кратко

JSON-столбец (jsonb в PostgreSQL) уместен для данных с переменной или заранее неизвестной структурой, которые читаются целиком и редко участвуют в связях: характеристики товаров разных категорий, настройки пользователя, сырые payload'ы вебхуков, метаданные. Не стоит класть в JSON то, по чему нужны связи, ограничения, частая фильтрация и агрегация — внешний ключ на поле JSON не повесить, NOT NULL/UNIQUE сложнее, статистика планировщика по ключам JSON слабая. jsonb хранится в бинарном разобранном виде, поддерживает операторы ->, ->>, @>, ?, jsonpath и GIN-индексы; json хранит исходный текст и нужен редко. Гибридный подход — стабильные поля в столбцах, вариативные — в jsonb.

Вопрос

Когда стоит хранить JSON в реляционной базе и как с ним работать?

Хорошие кандидаты

ДанныеПочему JSON подходит
Атрибуты товара (у ноутбука — RAM, у платья — размер)Структура зависит от категории
Настройки пользователя / feature flagsЧитаются целиком, часто меняется набор ключей
Payload вебхука, лог внешнего APIХранить «как пришло» для отладки
Снимок адреса доставки в заказеИсторический документ, не обновляется

Плохие кандидаты

ДанныеПочему
user_id, order_id внутри JSONНет FK, нет целостности
Статус, по которому фильтруют каждый запросЛучше столбец + индекс + CHECK
Массив позиций заказа, по которому строят отчётыСвязующая таблица удобнее и быстрее
Деньги, даты, по которым считают агрегатыТипы и ограничения теряются

Гибридная схема

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id bigint NOT NULL REFERENCES categories (id),
  title       text NOT NULL,
  price       numeric(12, 2) NOT NULL CHECK (price >= 0),
  attrs       jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(attrs) = 'object')
);

Запросы

SELECT title, attrs->>'brand' AS brand, (attrs->>'ram_gb')::int AS ram
FROM products
WHERE attrs @> '{"brand": "Lenovo"}'             -- содержит пару ключ-значение
  AND attrs ? 'ram_gb'                           -- есть ключ
  AND (attrs->>'ram_gb')::int >= 16;
 
-- jsonpath (PostgreSQL 12+)
SELECT * FROM products WHERE attrs @? '$.ports[*] ? (@ == "USB-C")';
 
-- обновление одного ключа
UPDATE products SET attrs = jsonb_set(attrs, '{color}', '"black"') WHERE id = 1;
UPDATE products SET attrs = attrs || '{"in_stock": true}' WHERE id = 1;
UPDATE products SET attrs = attrs - 'legacy_field';
ОператорЗначение
->Значение как jsonb
->>Значение как text
#>>'{a,b}'По пути, как text
@>Содержит
?Есть ключ

Индексы

-- для @>, ?, ?| (вхождение)
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
 
-- для частого фильтра по одному ключу
CREATE INDEX products_brand_idx ON products ((attrs->>'brand'));

Если поле фильтруется постоянно, его стоит «поднять» в обычный столбец (можно GENERATED ALWAYS AS (attrs->>'brand') STORED).

json или jsonb

jsonjsonb
ХранениеТекст как естьБинарный разобранный
Порядок ключей, дубли, пробелыСохраняютсяНормализуются
Индексы GIN, @>НетДа
КогдаНужен точный исходный текстПочти всегда

Частые ошибки

  • «Схема в JSON» для всех данных — по сути NoSQL без его преимуществ.
  • Внешние ключи внутри JSON.
  • Фильтрация по attrs->>'x' без индекса на больших таблицах.
  • Частые обновления одного ключа большого документа — переписывается вся строка (TOAST).

Что важно помнить

  • JSON — для гибких и документных данных, столбцы — для связей и частых фильтров.
  • В PostgreSQL используют jsonb.
  • GIN и индексы по выражению делают JSON-запросы быстрыми.

Мини-шпаргалка

  • ->, ->>, @>, ?, @?.
  • jsonb_set, ||, -.
  • USING gin (col jsonb_path_ops).
  • CHECK (jsonb_typeof(col) = 'object').

Документация

PostgreSQL: JSON Types, PostgreSQL: JSON Functions and Operators.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам