JSONB и массивы: гибкая структура — PostgreSQL 18 с

JSONB и массивы: где уместна гибкая структура

Реляционная модель хороша, когда у данных понятны столбцы, типы и связи. Но у товаров разных категорий могут быть разные дополнительные свойства: у монитора есть частота обновления, у клавиатуры — раскладка, у кресла — материал.

JSON — текстовый формат для вложенных объектов и списков. jsonb — тип PostgreSQL, который разбирает JSON и хранит его в двоичном виде, удобном для поиска и индексирования.

#Что оставить обычными столбцами

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id bigint NOT NULL,
    sku text NOT NULL,
    name text NOT NULL,
    price numeric(12, 2) NOT NULL CHECK (price >= 0),
    attributes jsonb NOT NULL DEFAULT '{}'::jsonb
        CHECK (jsonb_typeof(attributes) = 'object'),
    tags text[] NOT NULL DEFAULT ARRAY[]::text[],
    UNIQUE (org_id, sku)
);

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

Даже гибкое поле получает минимальное ограничение: jsonb_typeof требует объект вида {...}, а не число или список.

JSONB не должен скрывать обязательные деньги, количество, права или внешние ключи. Иначе база не сможет нормально проверить их тип и связь.

#Как получить значение из JSONB

SELECT
    attributes -> 'resolution' AS resolution_json,
    attributes ->> 'resolution' AS resolution_text
FROM products;

Оператор -> возвращает значение типа jsonb, а ->> — обычный text. Для числового сравнения текст нужно преобразовать:

SELECT id
FROM products
WHERE (attributes ->> 'hz')::integer >= 120;

::integer означает преобразование к целому числу. Если встретится строка "fast", запрос завершится ошибкой. Поэтому перед массовым преобразованием нужно проверить старые документы.

Если условие по hz стало частым и важным, лучше вынести значение в обычный integer-столбец. PostgreSQL сможет собрать по нему точную статистику и проверять тип при записи.

#Вхождение и наличие ключа

SELECT id, name
FROM products
WHERE attributes @> '{"wireless": true}'::jsonb;

SELECT id
FROM products
WHERE attributes ? 'layout';

@> проверяет, содержит ли левый JSONB указанную структуру. ? проверяет наличие ключа верхнего уровня.

Отсутствующий ключ и ключ со значением JSON null — разные состояния:

SELECT
    attributes ? 'color' AS key_exists,
    attributes -> 'color' AS json_value
FROM products;

Первое выражение отвечает, есть ли ключ, второе получает его значение.

#Поиск по вложенной структуре

Jsonpath — язык путей и условий внутри JSON:

SELECT id
FROM products
WHERE attributes @? '$.dimensions[*] ? (@ > 100)';

Запрос ищет документ, где хотя бы один элемент массива dimensions больше 100. Jsonpath удобен для сложной вложенности, но не любое выражение получает быстрый доступ через индекс. Это проверяют планом.

#GIN-индекс для JSONB

GIN — тип индекса, который хранит множество элементов одной строки. Он подходит для JSONB и массивов, где одна строка содержит несколько ключей или значений.

CREATE INDEX products_attributes_ops_idx
ON products USING gin (attributes);

CREATE INDEX products_attributes_path_idx
ON products USING gin (attributes jsonb_path_ops);

Это два разных класса операторов:

jsonb_ops поддерживает широкий набор операций, включая проверку ключа ?;
jsonb_path_ops обычно меньше и хорошо подходит для @> и части jsonpath, но поддерживает не все операции первого варианта.

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

Для одного популярного пути иногда достаточно меньшего индекса по выражению:

CREATE INDEX products_layout_idx
ON products ((attributes ->> 'layout'));

SELECT id
FROM products
WHERE attributes ->> 'layout' = 'ANSI';

Выражение в запросе должно совпадать с выражением индекса.

#Массивы

Массив хранит упорядоченный набор значений одного типа. Например, text[] — массив строк:

SELECT id
FROM products
WHERE tags @> ARRAY['keyboard']::text[];

CREATE INDEX products_tags_gin_idx ON products USING gin (tags);

Массив уместен для небольшого набора простых меток. Если у метки есть владелец, перевод, права или история, она становится отдельной сущностью и требует таблицы связи. Функция unnest(tags) разворачивает массив в строки, но не добавляет внешний ключ к каждому элементу.

#Обновление одного ключа всё равно меняет строку

UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{wireless}',
    'true'::jsonb,
    true
)
WHERE id = $1
RETURNING attributes;

jsonb_set возвращает новый документ с изменённым ключом. PostgreSQL создаёт новую версию всей строки; это не правка нескольких байтов на месте. Большие документы увеличивают объём WAL, работу индексов и нагрузку на хранение.

#Практика

В лаборатории 13 вы создадите каталог, проверите форму JSON-объекта, построите GIN с jsonb_path_ops и выполните поиск по структуре и массиву меток.

Подробнее: тип JSON, функции и операторы JSON, массивы.