Operadores de ruta JSONB en PostgreSQL: #> y #>> para valores anidados

Операторы #> и #>> в PostgreSQL достают значение из колонки jsonb по пути сразу через несколько уровней вложенности — одним выражением, без цепочки стрелок ->. Берёте их, когда нужное значение сидит на третьем-четвёртом уровне, под объектами и массивами, и тащить его звено за звеном неудобно: запрос превращается в лесенку, которую глаз отказывается читать. Разберём, чем #> отличается от #>>, как смешивать в пути ключи и индексы и в какой момент пора звать на помощь jsonb_path_query.

Что делают #> и #>>

Оба оператора принимают путь как массив текста text[] и спускаются по нему сверху вниз, звено за звеном. Различие ровно одно — тип результата:

• #> возвращает jsonb: вложенный объект, массив или скаляр, но всё ещё в виде JSON.
• #>> возвращает text: распакованный скаляр, без обрамляющих кавычек.

Пусть у users есть колонка profile jsonb вот такой формы: {"address": {"city": "Berlin", "geo": [52.5, 13.4]}, "tags": ["pro", "eu"]}.

-- jsonb result: still a JSON object
SELECT profile #> '{address}' AS address_json
FROM users
WHERE id = 1;

-- text result: the raw city string, no quotes
SELECT profile #>> '{address,city}' AS city
FROM users
WHERE id = 1;

Запомнить легко: лишняя > «выжимает» значение до чистого текста. В WHERE, JOIN и любых сравнениях вам почти всегда нужен #>> — ведь по другую сторону знака равенства стоит обычная строка, и сравнивать с ней jsonb напрямую неудобно.

Короче, чем цепочка ->

Главная выгода в том, что один путь складывает в себя несколько стрелок сразу. Поставьте два эквивалентных запроса рядом:

-- The arrow chain: noisy and easy to misread
SELECT profile -> 'address' ->> 'city' AS city
FROM users;

-- Same thing with a single path operator
SELECT profile #>> '{address,city}' AS city
FROM users;

Чем глубже структура, тем заметнее разрыв. Путь '{a,b,c,d}' читается как одно слово, а -> 'a' -> 'b' -> 'c' ->> 'd' приходится разбирать по звеньям и считать стрелки, чтобы не промахнуться последней — той самой, где -> обязан смениться на ->>.

Смешиваем ключи объектов и индексы массивов

В одном пути можно свободно чередовать ключи объектов и индексы массивов. Индексы пишутся числами в виде текста, отсчёт идёт с нуля, а отрицательные значения отсчитываются с конца — -1 это последний элемент.

-- First geo coordinate (array index 0) inside the address object
SELECT profile #>> '{address,geo,0}' AS latitude
FROM users
WHERE id = 1;

-- Last tag using a negative index
SELECT profile #>> '{tags,-1}' AS last_tag
FROM users
WHERE id = 1;

Ближе к жизни: в orders лежит meta jsonb со списком позиций, и нам нужен SKU первой позиции, но только по оплаченным заказам.

-- meta = {"items": [{"sku": "A-1", "qty": 2}, ...]}
SELECT o.id,
       o.meta #>> '{items,0,sku}' AS first_sku
FROM orders o
WHERE o.status = 'paid';

А вот и обещанные грабли. Если хоть одно звено пути не найдено — или вы обращаетесь к ключу как к индексу, а к индексу как к ключу, — оператор не падает с ошибкой, а молча отдаёт NULL. Снаружи это удобно, но ровно тут и прячется коварство: опечатка в имени ключа выглядит точно так же, как честное отсутствие данных. По одному результату «данных нет» и «путь кривой» не различить, так что написание ключей проверяйте глазами, а не по тому, вернулся NULL или нет.

Когда брать jsonb_path_query

Операторы пути берут ровно одно значение по жёстко заданному пути — и на этом их полномочия заканчиваются. Как только понадобились условия, обход всех элементов массива или маски-wildcard, переходите на jsonb_path_query и язык JSONPath (доступен с PostgreSQL 12).

-- All SKUs across the whole items array, one row each
SELECT o.id, sku.value AS sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku;

-- Path with a filter: items where qty > 1
SELECT o.id,
       jsonb_path_query(o.meta, '$.items[*] ? (@.qty > 1)') AS big_item
FROM orders o;

Короткий ориентир, что когда хватать:

• Знаете точный путь и хотите одно значение — #> или #>>.
• Нужны фильтры, [*] или несколько совпадений сразу — jsonb_path_query.
• Достаточно ответа «да/нет» прямо в WHERE — оператор @? с JSONPath короче и ложится под GIN-индекс.

Есть и структурное отличие, о которое легко споткнуться. jsonb_path_query на каждое совпадение отдаёт отдельную строку, поэтому в SELECT его обычно подключают через FROM как функцию, возвращающую набор строк. А #> и #>> остаются скалярными выражениями и спокойно живут где угодно — прямо в списке колонок, в WHERE, в GROUP BY. Подставлять функцию-набор туда, где ждут одно значение, придётся аккуратно: поведение будет совсем не таким, как у скалярного оператора.

И последнее, про производительность. Ускорить можно и то и другое: под фиксированный путь делается индекс по выражению (CREATE INDEX ON users ((profile #>> '{address,city}'))), под гибкие запросы — GIN с классом операторов jsonb_path_ops. Правило простое: если путь всегда один и тот же, индекс по выражению почти наверняка обойдёт GIN и по размеру, и по скорости; GIN же оправдан там, где заранее не знаешь, какой ключ спросят. И ещё штрих на будущее: всё это — чистая специфика PostgreSQL. Ни MySQL, ни ClickHouse операторов #>/#>> не знают, у них для вложенного JSON свои функции вроде JSON_EXTRACT и JSONExtract, так что переносить такие запросы один в один не выйдет.