jsonb это двоичный тип столбца для JSON в PostgreSQL. Поля читают через -> (возвращает jsonb) и ->> (возвращает text), строки находят оператором вхождения @>, а поля меняют через jsonb_set или ||. Индекс GIN делает такие запросы быстрыми.
Подходят заказы 1 и 3. data->'customer' возвращает вложенный объект как jsonb, ->>'name' достаёт имя в виде текста, а ->> с приведением типа превращает total в число.
Достаём значения из JSONB
| Оператор | Возвращает | Пример |
|---|---|---|
-> ключ или индекс | jsonb | data->'customer', data->'tags'->0 |
->> ключ или индекс | text | data->>'status' |
#> путь | jsonb | data#>'{customer,city}' |
#>> путь | text | data#>>'{customer,city}' |
[...] индексация (PostgreSQL 14+) | jsonb | data['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, когда новое значение это SQLNULL, и тогдаUPDATEстирает весь столбец. ПередавайтеCOALESCE(to_jsonb(x), 'null')или используйтеjsonb_set_lax(PostgreSQL 13+), который по умолчанию сохраняет JSONnull.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
json | jsonb | |
|---|---|---|
| Как хранится | Исходный текст | Разобранная двоичная форма |
| Пробелы, порядок ключей, повторяющиеся ключи | Сохраняются | Удаляются; побеждает последний дубль |
| Запись | Чуть быстрее (только проверка) | Разбирается при вставке |
| Чтение и запросы | Разбирается заново при каждом обращении | Быстро |
@>, ? и индексы 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')), но внешнему ключу нужен настоящий столбец. Поля, которые есть в каждой строке и на которые ссылаются другие таблицы, место в обычных столбцах.