POSTGRESQL
#postgresql#jsonb#gin#index#json

PostgreSQL: JSONB и GIN-индекс — таблица, запросы, индексы

Создание таблицы с JSONB, GIN-индекс для быстрого поиска, операторы @> и ?. Копируй — вставляй — работает. По документации PostgreSQL.

Как использовать

  1. Создай таблицу с полем JSONB и при необходимости GIN-индекс (см. блоки ниже).
  2. Поиск по вхождению объекта: WHERE attrs @> '{"color": "red"}'.
  3. Проверка ключа: WHERE attrs ? 'color'. Извлечение: attrs->>'brand'.

Работа с полем JSONB и GIN-индексом для быстрого поиска. По JSON Types и GIN Indexes.

JSONB хранится в разобранном бинарном виде и поддерживает индексы; GIN ускоряет операторы содержания (@>, ?, ?&, ?|).

1. Таблица с JSONB

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attrs JSONB
);

-- Опционально: ограничение на структуру через CHECK (PostgreSQL 12+)
-- ALTER TABLE products ADD CONSTRAINT attrs_is_object CHECK (jsonb_typeof(attrs) = 'object');

2. GIN-индекс для поиска по JSONB

Для операторов @> (содержит), ? (есть ключ), ?&, ?|:

CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs);

Один индекс поддерживает все перечисленные операторы. При частых запросах по одному ключу можно использовать выражение:

CREATE INDEX idx_products_attrs_brand ON products USING GIN ((attrs -> 'brand'));

3. Запросы (копируй и подставляй свои поля)

Содержит пару ключ–значение (и целый объект):

SELECT * FROM products WHERE attrs @> '{"color": "red"}';

-- Несколько полей
SELECT * FROM products WHERE attrs @> '{"color": "red", "size": "M"}';

Есть ключ:

SELECT * FROM products WHERE attrs ? 'color';

Извлечь значение как текст и фильтровать:

SELECT * FROM products WHERE attrs->>'brand' = 'Apple';

Агрегация по полю из JSONB:

SELECT attrs->>'brand' AS brand, COUNT(*), AVG((attrs->>'price')::numeric)
FROM products
WHERE attrs ? 'price'
GROUP BY attrs->>'brand';

4. Полный пример: таблица + индекс + запросы

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

CREATE INDEX idx_products_attrs_gin ON products USING GIN (attrs);

INSERT INTO products (name, attrs) VALUES
  ('iPhone', '{"brand": "Apple", "color": "black"}'),
  ('Galaxy', '{"brand": "Samsung", "color": "blue"}');

-- Поиск по вхождению
SELECT * FROM products WHERE attrs @> '{"color": "black"}';

-- Поиск по ключу
SELECT * FROM products WHERE attrs ? 'brand';

-- Текст по ключу
SELECT name, attrs->>'brand' AS brand FROM products;

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

Похожие сниппеты