Menu

JSONB в PostgreSQL: операторы, запросы и индексы

Храните JSON в PostgreSQL и делайте по нему запросы с jsonb: операторы -> и ->>, вхождение через @>, jsonb_set и другие обновления, массивы в строки, индексы GIN и разница между json и jsonb.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

jsonb это двоичный тип столбца для JSON в PostgreSQL. Поля читают через -> (возвращает jsonb) и ->> (возвращает text), строки находят оператором вхождения @>, а поля меняют через jsonb_set или ||. Индекс GIN делает такие запросы быстрыми.

Подходят заказы 1 и 3. data->'customer' возвращает вложенный объект как jsonb, ->>'name' достаёт имя в виде текста, а ->> с приведением типа превращает total в число.

Достаём значения из JSONB

ОператорВозвращаетПример
-> ключ или индексjsonbdata->'customer', data->'tags'->0
->> ключ или индексtextdata->>'status'
#> путьjsonbdata#>'{customer,city}'
#>> путьtextdata#>>'{customer,city}'
[...] индексация (PostgreSQL 14+)jsonbdata['customer']['city']

Индексы массива начинаются с 0, а отрицательный индекс считает с конца (-> -1 это последний элемент). Отсутствующий ключ даёт SQL NULL, а не ошибку, поэтому у заказа 3 выше город NULL.

Используйте ->> для сравнения с текстом или приведения типа: data->>'status' = 'paid'. Используйте ->, чтобы идти дальше по структуре. Частая ошибка: data->'status' = 'paid'. PostgreSQL читает 'paid' как JSON, слово без кавычек не является корректным JSON, и запрос падает с ошибкой «invalid input syntax for type json».

Поиск строк по содержимому JSON

@> проверяет, содержит ли левое значение правое. Он работает на любой глубине и внутри массивов, и его поддерживает индекс GIN:

У товаров 1 и 2 есть размер M и цена; дороже 30 только товар 2. Операторы поиска:

  • @> содержит, <@ содержится в.
  • ? ключ (или строка массива) существует, ?| существует любой из ключей, ?& существуют все.
  • @? и @@ выполняют запрос JSON path (PostgreSQL 12+) для сравнений внутри документа.

Обновление полей внутри JSONB

При каждом обновлении PostgreSQL перезаписывает всё значение, но эти функции позволяют описать только изменение:

  • jsonb_set(target, path, new_value) заменяет одно значение по пути.
  • - удаляет ключ, а #- удаляет вложенный путь, например '{notify,sms}'.
  • || добавляет или перезаписывает ключи верхнего уровня.

Начиная с PostgreSQL 14, присваивать можно и через индексацию: SET prefs['notify']['email'] = 'false'.

Две ловушки:

  • jsonb_set возвращает NULL, когда новое значение это SQL NULL, и тогда UPDATE стирает весь столбец. Передавайте COALESCE(to_jsonb(x), 'null') или используйте jsonb_set_lax (PostgreSQL 13+), который по умолчанию сохраняет JSON null.
  • jsonb_set добавляет отсутствующий последний ключ, но не отсутствующих родителей: установка '{a,b}' в {} ничего не меняет. Присваивание через индексацию их создаёт.

Массивы JSON в строки

jsonb_array_elements возвращает по строке на каждый элемент массива, а jsonb_to_recordset превращает массив объектов в типизированные столбцы. Начиная с PostgreSQL 17, стандартный JSON_TABLE делает и то и другое в одном месте:

A-1 продан в количестве 3, B-2 в количестве 1. В обратную сторону jsonb_build_object('id', id, 'name', name) собирает объект из столбцов, а jsonb_agg собирает строки в массив (см. string_agg и array_agg).

Индекс GIN для JSONB

Индекс GIN на весь столбец обслуживает @>, ?, ?|, ?&, @? и @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

Пишите запрос в той форме, которую поддерживает индекс. WHERE data @> '{"status": "paid"}' может использовать индекс GIN. WHERE data->>'status' = 'paid' не может, ему нужен индекс по выражению. Проверке диапазона вроде (data->>'total')::numeric > 50 нужен индекс именно по этому выражению.

JSON или JSONB

jsonjsonb
Как хранитсяИсходный текстРазобранная двоичная форма
Пробелы, порядок ключей, повторяющиеся ключиСохраняютсяУдаляются; побеждает последний дубль
ЗаписьЧуть быстрее (только проверка)Разбирается при вставке
Чтение и запросыРазбирается заново при каждом обращенииБыстро
@>, ? и индексы GINНетДа

json возвращает текст без изменений; jsonb оставляет только "a": 3 и меняет порядок ключей. Берите jsonb, если только вам не нужно вернуть исходный текст байт в байт, как в сохранённом payload вебхука. Поля, которые есть в каждой строке или которым нужен внешний ключ, держите в обычных столбцах. В официальном справочнике по функциям JSON перечислены все операторы и функции.

Часто задаваемые вопросы

Чем -> отличается от ->> в PostgreSQL?

-> возвращает значение как jsonb, и по нему можно идти дальше (data->'customer'->'city'). ->> возвращает его как text, а это то, что сравнивают со строкой или приводят к числу: (data->>'total')::numeric.

Как проверить, что массив JSONB содержит значение?

Используйте вхождение: data->'tags' @> '["gift"]' или data @> '{"tags": ["gift"]}', чтобы проверять от корня документа. Для массива строк работает и data->'tags' ? 'gift'. Индекс GIN на data ускоряет вторую форму.

Сохраняет ли JSONB порядок ключей?

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

Как проверить, есть ли ключ в JSONB?

data ? 'key' истинно, когда у объекта верхнего уровня есть этот ключ, даже если его значение JSON null. ?| принимает массив и истинно, если есть хотя бы один ключ, ?& если есть все.

Можно ли повесить внешний ключ или NOT NULL на поле внутри JSONB?

Напрямую нет. Ограничение CHECK может проверять поле (CHECK (data ? 'email')), но внешнему ключу нужен настоящий столбец. Поля, которые есть в каждой строке и на которые ссылаются другие таблицы, место в обычных столбцах.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать