JSONB в PostgreSQL: хранение и запросы к полуструктурированным данным

PostgreSQL поддерживает два JSON-типа: json и jsonb. На практике почти всегда нужен jsonb — он хранит данные в бинарном формате, поддерживает индексы и работает быстрее при запросах.

JSON vs JSONB

Вывод: используй jsonb везде, кроме случаев когда важно сохранить исходный порядок ключей или дублирующиеся ключи.

Операторы для работы с JSONB

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('Ноутбук', '{"brand": "Dell", "specs": {"ram": 16, "ssd": 512}, "tags": ["работа", "портативный"]}'),
('Телефон', '{"brand": "Samsung", "specs": {"ram": 8, "ssd": 256}, "tags": ["мобильный"]}'),
('Монитор', '{"brand": "LG", "specs": {"size": 27, "resolution": "4K"}, "tags": ["офис", "работа"]}');

Извлечение значений

-- -> возвращает JSON-объект или массив
SELECT attributes -> 'brand' FROM products;
-- "Dell", "Samsung", "LG"  (тип jsonb)

-- ->> возвращает текст
SELECT attributes ->> 'brand' FROM products;
-- Dell, Samsung, LG  (тип text)

-- Вложенные поля через цепочку ->
SELECT attributes -> 'specs' -> 'ram' FROM products;
-- 16, 8, null

-- #> и #>> для пути через массив ключей
SELECT attributes #>> '{specs, ram}' FROM products;
-- 16, 8, null  (тип text)

Проверка наличия ключей и значений

-- ? проверяет наличие ключа на верхнем уровне
SELECT name FROM products WHERE attributes ? 'brand';

-- @> содержит ли (containment) — самый полезный оператор
SELECT name FROM products
WHERE attributes @> '{"brand": "Dell"}';
-- Ноутбук

-- Поиск по вложенному полю
SELECT name FROM products
WHERE attributes @> '{"specs": {"ram": 16}}';
-- Ноутбук

-- ?| хотя бы один из ключей присутствует
SELECT name FROM products WHERE attributes ?| ARRAY['brand', 'price'];

-- ?& все ключи присутствуют
SELECT name FROM products WHERE attributes ?& ARRAY['brand', 'specs'];

GIN-индекс

Без индекса @> делает полное сканирование. GIN-индекс делает его быстрым:

-- Индекс по всему JSONB (поддерживает @>, ?, ?|, ?&)
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Индекс только по конкретному полю
CREATE INDEX idx_products_brand ON products ((attributes ->> 'brand'));

-- jsonb_path_ops — компактнее, поддерживает только @>
CREATE INDEX idx_products_attrs_path ON products
USING GIN (attributes jsonb_path_ops);

jsonb_path_ops создаёт меньший индекс и быстрее работает с @>, но не поддерживает операторы ?, ?|, ?&.

Работа с массивами: jsonb_array_elements

-- Развернуть массив тегов в строки
SELECT p.name, tag
FROM products p,
     jsonb_array_elements_text(p.attributes -> 'tags') AS tag;

name       | tag
-----------|------------
Ноутбук    | работа
Ноутбук    | портативный
Телефон    | мобильный
Монитор    | офис
Монитор    | работа

-- Найти все продукты с тегом "работа"
SELECT name FROM products
WHERE attributes -> 'tags' @> '"работа"';

-- Посчитать количество тегов у каждого продукта
SELECT name, jsonb_array_length(attributes -> 'tags') AS tag_count
FROM products;

Обновление полей: jsonb_set

jsonb неизменяемый тип — нельзя обновить одно поле, не заменив весь объект. jsonb_set делает это удобно:

-- Обновить вложенное поле
UPDATE products
SET attributes = jsonb_set(attributes, '{specs, ram}', '32')
WHERE name = 'Ноутбук';

-- Добавить новый ключ
UPDATE products
SET attributes = jsonb_set(attributes, '{price}', '89999', true)
WHERE name = 'Ноутбук';

-- Удалить ключ (оператор -)
UPDATE products
SET attributes = attributes - 'price'
WHERE name = 'Ноутбук';

-- Слить два JSONB объекта (|| перезаписывает существующие ключи)
UPDATE products
SET attributes = attributes || '{"in_stock": true, "warehouse": "Moscow"}'
WHERE id = 1;

Агрегация и построение JSON

-- Собрать строки в JSON-массив
SELECT jsonb_agg(name) AS product_names FROM products;
-- ["Ноутбук", "Телефон", "Монитор"]

-- Собрать объект из пар ключ-значение
SELECT jsonb_object_agg(name, attributes ->> 'brand') AS name_to_brand
FROM products;
-- {"Ноутбук": "Dell", "Телефон": "Samsung", "Монитор": "LG"}

-- Построить объект из колонок
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'brand', attributes ->> 'brand'
) FROM products;

Когда использовать JSONB, а когда отдельные колонки

Используй JSONB когда:

• Структура данных нестабильна или различается между строками
• Поля появляются и исчезают (настройки пользователя, метаданные событий)
• Нужно хранить произвольные атрибуты без изменения схемы
• Данные приходят из внешних API и используются как есть

Используй отдельные колонки когда:

• Поле присутствует у большинства строк
• По полю часто фильтруют или сортируют
• Поле используется в JOIN или агрегации
• Важны ограничения (NOT NULL, CHECK, FOREIGN KEY)

-- Плохо: частый запрос по полю внутри JSONB
SELECT * FROM products WHERE (attributes ->> 'price')::numeric > 50000;

-- Лучше: вынести price в отдельную колонку
ALTER TABLE products ADD COLUMN price NUMERIC;
CREATE INDEX idx_products_price ON products (price);

Практические советы

1. Всегда создавай GIN-индекс на jsonb-колонках, по которым ищешь через @>
2. Для частых запросов по конкретному ключу — expression-индекс по attributes ->> 'key'
3. jsonb_path_ops в GIN быстрее для @> и занимает меньше места
4. Используй jsonb_strip_nulls перед сохранением чтобы не хранить null-поля
5. \d+ tablename в psql показывает тип и размер jsonb-колонки

Попрактикуйся с JSONB-запросами в нашем тренажёре.