Операторы #> и #>> в PostgreSQL: как доставать данные из вложенного jsonb

Когда данные лежат в обычных колонках таблицы, всё просто:

SELECT email
FROM users;

Но в реальных проектах часть информации часто хранится внутри jsonb: профиль пользователя, настройки, метаданные заказа, ответ внешнего сервиса, список товаров в корзине.

Например, в колонке profile может лежать такой JSON:

{
  "address": {
    "city": "Berlin",
    "geo": [52.5, 13.4]
  },
  "tags": ["pro", "eu"]
}

И вот тут возникает задача: достать не весь JSON целиком, а конкретное значение где-то внутри.

Например:

• город пользователя;
• первую координату;
• последний тег;
• sku первой позиции заказа;
• вложенный объект с адресом.

В PostgreSQL для этого есть операторы #> и #>>. Они достают значение из jsonb по пути сразу через несколько уровней вложенности.

Главная польза в том, что не нужно строить длинную лесенку из стрелок -> и ->>. Путь можно записать одним аккуратным выражением.

Зачем нужны #> и #>>

Представьте, что JSON — это шкаф с ящиками.

Чтобы добраться до города, нужно пройти путь:

1. открыть address;
2. внутри него взять city.

Можно идти по шагам через стрелки:

SELECT profile -> 'address' ->> 'city' AS city
FROM users;

А можно записать весь путь сразу:

SELECT profile #>> '{address,city}' AS city
FROM users;

Вот для этого и нужны #> и #>>.

Они говорят PostgreSQL:

Возьми JSON и спустись по этому пути: сначала сюда, потом сюда, потом сюда.

Путь передаётся как массив текста типа text[]. На практике чаще всего его записывают вот так:

'{address,city}'

Это значит: сначала ключ address, потом ключ city.

Разница между #> и #>>

Операторы похожи, но возвращают разные типы результата.

#> возвращает результат как jsonb.

#>> возвращает результат как text.

Проще говоря:

• #> достаёт кусок JSON;
• #>> достаёт обычный текст.

Посмотрим на примере.

Есть таблица users с колонкой profile jsonb.

SELECT
  profile #> '{address}' AS address_json
FROM users
WHERE id = 1;

Результат будет JSON-объектом:

{"city": "Berlin", "geo": [52.5, 13.4]}

А теперь достанем город как текст:

SELECT
  profile #>> '{address,city}' AS city
FROM users
WHERE id = 1;

Результат:

Berlin

Без JSON-кавычек, без объекта, без лишней упаковки. Просто строка.

Запомнить можно так:

один > оставляет JSON, два >> вытаскивают значение наружу как текст.

То же правило работает и со стрелками:

• -> возвращает JSON;
• ->> возвращает текст;
• #> возвращает JSON по пути;
• #>> возвращает текст по пути.

Когда использовать #>, а когда #>>

Если нужно получить вложенный объект или массив и дальше работать с ним как с JSON, берите #>.

SELECT
  profile #> '{address}' AS address
FROM users;

Такой результат всё ещё остаётся типом jsonb.

Если нужно сравнивать значение, показывать его в отчёте, использовать в WHERE, JOIN, GROUP BY или сортировке, чаще нужен #>>.

SELECT
  id,
  profile #>> '{address,city}' AS city
FROM users
WHERE profile #>> '{address,city}' = 'Berlin';

Почему здесь лучше #>>?

Потому что справа от = стоит обычная строка 'Berlin'. Удобнее сравнивать текст с текстом, а не JSON со строкой.

Почему это удобнее цепочки стрелок

Для коротких путей разница кажется небольшой.

SELECT profile -> 'address' ->> 'city' AS city
FROM users;

И так:

SELECT profile #>> '{address,city}' AS city
FROM users;

Оба запроса делают одно и то же.

Но чем глубже JSON, тем заметнее становится выгода.

Например, нужно достать значение из такого пути:

settings.notifications.email.weekly.enabled

Через стрелки получится длинная цепочка:

SELECT
  profile -> 'settings' -> 'notifications' -> 'email' -> 'weekly' ->> 'enabled' AS weekly_enabled
FROM users;

А через #>>:

SELECT
  profile #>> '{settings,notifications,email,weekly,enabled}' AS weekly_enabled
FROM users;

Второй вариант легче читать: путь виден целиком, как адрес.

У цепочки стрелок есть ещё одна неприятность: последняя стрелка часто должна отличаться от предыдущих. Пока вы спускаетесь по JSON, используете ->. Когда хотите получить текст — ставите ->>.

В длинной цепочке легко промахнуться.

С #>> проще: весь путь один, а тип результата задаётся оператором.

Как писать путь

Путь для #> и #>> записывается в фигурных скобках:

'{address,city}'

Каждый элемент пути — это следующий шаг внутрь JSON.

Например:

{
  "address": {
    "city": "Berlin"
  }
}

Путь до города:

'{address,city}'

Если структура глубже:

{
  "company": {
    "department": {
      "manager": {
        "name": "Alice"
      }
    }
  }
}

Путь до имени менеджера:

'{company,department,manager,name}'

Запрос:

SELECT
  profile #>> '{company,department,manager,name}' AS manager_name
FROM users;

Ключи объектов и индексы массивов в одном пути

JSON может содержать не только объекты, но и массивы.

Например:

{
  "address": {
    "city": "Berlin",
    "geo": [52.5, 13.4]
  },
  "tags": ["pro", "eu"]
}

Здесь address — объект, geo — массив, tags — массив.

В пути можно спокойно смешивать ключи объектов и индексы массивов.

Индексы массивов начинаются с нуля.

Чтобы достать первую координату из geo, нужен индекс 0:

SELECT
  profile #>> '{address,geo,0}' AS latitude
FROM users
WHERE id = 1;

Чтобы достать вторую координату, нужен индекс 1:

SELECT
  profile #>> '{address,geo,1}' AS longitude
FROM users
WHERE id = 1;

Обратите внимание: индекс внутри пути тоже пишется как часть текстового массива. Поэтому в пути он выглядит как 0 или 1.

Отрицательные индексы

PostgreSQL умеет обращаться к элементам массива с конца.

Индекс -1 означает последний элемент.

SELECT
  profile #>> '{tags,-1}' AS last_tag
FROM users
WHERE id = 1;

Если в tags лежит такой массив:

["pro", "eu"]

то результатом будет:

eu

Это удобно, когда нужен последний элемент массива и вы не хотите заранее узнавать его длину.

Пример ближе к жизни: SKU первой позиции заказа

Представим таблицу orders.

В ней есть колонка meta jsonb, где лежит список товаров заказа:

{
  "items": [
    {
      "sku": "A-1",
      "qty": 2
    },
    {
      "sku": "B-7",
      "qty": 1
    }
  ]
}

Нужно достать sku первой позиции.

Путь такой:

1. items;
2. элемент массива с индексом 0;
3. ключ sku.

Запрос:

SELECT
  id,
  meta #>> '{items,0,sku}' AS first_sku
FROM orders
WHERE status = 'paid';

Получится обычная текстовая колонка first_sku.

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

Что будет, если путь неправильный

Операторы #> и #>> не падают с ошибкой, если путь не найден.

Они возвращают NULL.

Например, в JSON нет ключа address:

{
  "name": "Alice"
}

Запрос:

SELECT
  profile #>> '{address,city}' AS city
FROM users;

вернёт NULL.

То же самое будет, если вы ошиблись в названии ключа:

SELECT
  profile #>> '{adress,city}' AS city
FROM users;

Здесь в слове address пропущена буква. PostgreSQL не скажет: «ключ написан неправильно». Он просто вернёт NULL.

С одной стороны, это удобно: запрос не ломается из-за отсутствующих данных.

С другой стороны, это опасно: честное отсутствие данных и опечатка в пути выглядят одинаково.

Поэтому, когда вы впервые пишете путь к вложенному JSON, полезно сначала посмотреть кусок JSON целиком:

SELECT
  profile
FROM users
WHERE id = 1;

А потом уже доставать нужное значение.

NULL не всегда значит «данных нет»

Вот типичная ситуация.

Вы пишете:

SELECT
  id,
  profile #>> '{address,city}' AS city
FROM users;

В некоторых строках city оказался NULL.

Причин может быть несколько:

• у пользователя действительно нет адреса;
• есть адрес, но нет города;
• ключ называется не city, а иначе;
• в одной строке структура JSON отличается от остальных;
• в пути опечатка;
• на месте объекта лежит массив или строка.

По одному NULL нельзя понять, какая именно причина сработала.

Если данные важные, проверяйте структуру отдельно.

Например, можно посмотреть строки, где город не найден:

SELECT
  id,
  profile
FROM users
WHERE profile #>> '{address,city}' IS NULL;

Так быстрее увидеть, проблема в данных или в запросе.

#> удобно использовать для вложенных объектов

Не всегда нужно доставать скалярное значение вроде строки или числа. Иногда нужен целый вложенный объект.

Например, весь адрес:

SELECT
  id,
  profile #> '{address}' AS address
FROM users;

Результат останется JSON-объектом.

Это удобно, если вы хотите передать дальше часть JSON, сохранить её, проверить наличие ключа или применить к ней другие JSON-операторы.

Например, можно достать объект address, а затем проверить, есть ли в нём ключ city:

SELECT
  id
FROM users
WHERE profile #> '{address}' ? 'city';

Здесь #> уместнее, потому что оператор ? работает с jsonb, а не с обычным текстом.

#>> удобно использовать в WHERE

Чаще всего в прикладных запросах нужен именно #>>.

Например, найти пользователей из Берлина:

SELECT
  id,
  email
FROM users
WHERE profile #>> '{address,city}' = 'Berlin';

Или отфильтровать заказы по источнику из метаданных:

SELECT
  id,
  amount
FROM orders
WHERE meta #>> '{source}' = 'mobile';

Или сгруппировать пользователей по городу:

SELECT
  profile #>> '{address,city}' AS city,
  COUNT(*) AS users_count
FROM users
GROUP BY profile #>> '{address,city}'
ORDER BY users_count DESC;

Такой запрос превращает вложенное поле JSON в обычное значение, с которым можно работать как с колонкой.

Если нужно число, текст придётся привести к числу

#>> возвращает text.

Даже если внутри JSON лежит число, после #>> оно станет текстом.

Например:

{
  "age": 31
}

Запрос:

SELECT
  profile #>> '{age}' AS age
FROM users;

вернёт текстовое значение.

Если нужно сравнивать как число, используйте приведение типа:

SELECT
  id
FROM users
WHERE (profile #>> '{age}')::int >= 18;

Скобки здесь важны: сначала достаём значение из JSON, потом приводим его к int.

То же самое с ценами, рейтингами, количеством товаров и другими числовыми значениями.

SELECT
  id,
  (meta #>> '{items,0,qty}')::int AS first_item_qty
FROM orders;

Но будьте аккуратны: если в JSON окажется не число, а строка вроде "many", приведение к int закончится ошибкой.

Когда #> и #>> уже не хватает

Операторы #> и #>> хороши, когда вы знаете точный путь и хотите достать одно значение.

Например:

meta #>> '{items,0,sku}'

Это значит:

Возьми sku у первой позиции заказа.

Но иногда задача сложнее.

Например:

• достать sku у всех позиций заказа;
• найти все товары, где qty > 1;
• пройтись по массиву любой длины;
• использовать условия внутри JSON;
• найти несколько совпадений, а не одно.

Для таких задач лучше подходит JSONPath и функция jsonb_path_query.

JSONPath доступен в PostgreSQL начиная с версии 12.

jsonb_path_query: когда нужно пройти по массиву

Допустим, в meta лежит заказ с массивом items:

{
  "items": [
    {
      "sku": "A-1",
      "qty": 2
    },
    {
      "sku": "B-7",
      "qty": 1
    }
  ]
}

Если написать так:

SELECT
  id,
  meta #>> '{items,0,sku}' AS first_sku
FROM orders;

мы получим только первый sku.

А если нужны все sku, используем jsonb_path_query:

SELECT
  o.id,
  sku.value AS sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku(value);

Здесь [*] означает:

пройти по всем элементам массива.

Если в заказе две позиции, функция вернёт две строки для этого заказа.

JSONPath с условием

JSONPath умеет не только ходить по структуре, но и фильтровать элементы.

Например, нужно достать позиции заказа, где количество больше 1.

SELECT
  o.id,
  item.value AS big_item
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*] ? (@.qty > 1)') AS item(value);

Здесь выражение:

$.items[*] ? (@.qty > 1)

означает:

• взять items;
• пройти по всем элементам массива;
• оставить только элементы, где qty > 1.

Это уже задача не для #>>. Оператор пути умеет идти по точному адресу, но не умеет сам обходить массив и выбирать элементы по условию.

Важное отличие: скаляр и набор строк

#> и #>> возвращают одно значение.

Например:

SELECT
  meta #>> '{items,0,sku}' AS first_sku
FROM orders;

На каждый заказ будет одна строка и одно значение.

А jsonb_path_query может вернуть несколько значений.

Например:

SELECT
  o.id,
  sku.value AS sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku(value);

Если в заказе три товара, по этому заказу может появиться три строки.

Это очень важная разница.

#>> — скалярное выражение. Его удобно писать прямо в SELECT, WHERE, GROUP BY, ORDER BY.

jsonb_path_query — функция, которая возвращает набор строк. Поэтому её часто используют в FROM.

Короткий выбор инструмента

Если путь точный и нужно одно значение, используйте #> или #>>.

SELECT
  profile #>> '{address,city}' AS city
FROM users;

Если нужно получить вложенный объект или массив как JSON, используйте #>.

SELECT
  profile #> '{address}' AS address
FROM users;

Если нужно получить текстовое значение для сравнения, отчёта или группировки, используйте #>>.

SELECT
  profile #>> '{address,city}' AS city
FROM users;

Если нужно пройти по массиву, найти несколько совпадений или применить условие внутри JSON, используйте jsonb_path_query.

SELECT
  o.id,
  sku.value AS sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku(value);

Если нужен короткий ответ «подходит JSON под условие или нет», можно использовать оператор @?.

SELECT
  id
FROM orders
WHERE meta @? '$.items[*] ? (@.qty > 1)';

Такой вариант хорошо подходит для фильтрации строк.

Индекс по фиксированному пути

Если вы часто фильтруете по одному и тому же пути, можно сделать индекс по выражению.

Например, часто ищете пользователей по городу:

SELECT
  id,
  email
FROM users
WHERE profile #>> '{address,city}' = 'Berlin';

Тогда можно создать индекс:

CREATE INDEX users_profile_city_idx
ON users ((profile #>> '{address,city}'));

После этого PostgreSQL сможет быстрее искать строки по этому выражению.

Такой индекс хорош, когда путь фиксированный и важный для запросов.

Например:

• город пользователя;
• язык интерфейса;
• источник заказа;
• внешний id;
• тип события.

Индекс по выражению обычно компактнее и понятнее, чем большой универсальный индекс по всему JSON.

GIN-индекс для более гибких JSON-запросов

Если запросы по JSON разные и заранее неизвестно, какие ключи будут спрашивать, часто смотрят в сторону GIN.

Например:

CREATE INDEX orders_meta_gin_idx
ON orders
USING GIN (meta jsonb_path_ops);

Такой индекс может быть полезен для запросов, где вы проверяете структуру JSON, наличие вложенных значений или используете JSONPath-условия.

Но не стоит думать, что один GIN-индекс всегда лучше всего.

Правило простое:

если путь всегда один и тот же, начните с индекса по выражению; если запросы гибкие и разные, смотрите в сторону GIN.

Для конкретного проекта всё равно нужно проверять план запроса через EXPLAIN ANALYZE.

Переносимость в другие СУБД

Операторы #> и #>> — это синтаксис PostgreSQL.

В MySQL такие запросы один в один не перенесутся. Там для работы с JSON используют функции вроде JSON_EXTRACT и операторы своего диалекта.

В ClickHouse тоже другой набор инструментов: функции семейства JSONExtract.

Поэтому, если вы пишете запросы именно под PostgreSQL, #> и #>> — удобный и выразительный инструмент. Но если код должен жить сразу в нескольких СУБД, лучше заранее учитывать, что JSON-синтаксис у баз заметно отличается.

Частые ошибки новичков

Первая ошибка — использовать #> там, где нужен текст.

SELECT
  id
FROM users
WHERE profile #> '{address,city}' = 'Berlin';

Так сравнивать неудобно, потому что слева JSON, а справа обычная строка.

Лучше так:

SELECT
  id
FROM users
WHERE profile #>> '{address,city}' = 'Berlin';

Вторая ошибка — забывать, что массивы считаются с нуля.

SELECT
  meta #>> '{items,1,sku}' AS sku
FROM orders;

Так вы достанете не первый товар, а второй.

Для первого товара нужен индекс 0:

SELECT
  meta #>> '{items,0,sku}' AS sku
FROM orders;

Третья ошибка — считать, что NULL всегда означает отсутствие данных.

SELECT
  profile #>> '{address,city}' AS city
FROM users;

Если вернулся NULL, это может быть и пустой адрес, и опечатка в ключе, и другая структура JSON.

Четвёртая ошибка — пытаться через #>> достать все элементы массива.

SELECT
  meta #>> '{items,sku}' AS sku
FROM orders;

Так не получится, потому что items — массив, а не объект. Нужно указать конкретный индекс:

SELECT
  meta #>> '{items,0,sku}' AS sku
FROM orders;

Или использовать JSONPath, если нужны все элементы:

SELECT
  o.id,
  sku.value AS sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku(value);

Главное

Операторы #> и #>> нужны, чтобы доставать данные из jsonb по пути через несколько уровней вложенности.

#> возвращает результат как jsonb.

SELECT
  profile #> '{address}' AS address
FROM users;

#>> возвращает результат как text.

SELECT
  profile #>> '{address,city}' AS city
FROM users;

Если нужно сравнивать значение, показывать его в отчёте, группировать или сортировать, чаще всего нужен #>>.

Если нужно сохранить результат как JSON-объект или JSON-массив, берите #>.

В пути можно смешивать ключи объектов и индексы массивов:

SELECT
  profile #>> '{address,geo,0}' AS latitude,
  profile #>> '{tags,-1}' AS last_tag
FROM users;

Если путь не найден, PostgreSQL вернёт NULL, а не ошибку. Это удобно, но опечатки в ключах из-за этого легко не заметить.

#> и #>> хороши для точного пути и одного значения. Если нужно обходить массивы, искать несколько совпадений или фильтровать элементы внутри JSON, используйте jsonb_path_query или JSONPath-операторы вроде @?.

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

CREATE INDEX users_profile_city_idx
ON users ((profile #>> '{address,city}'));

Итог простой: когда нужное значение лежит глубоко внутри jsonb, не стройте длинную лестницу из стрелок. Запишите путь целиком через #> или #>> — запрос станет короче, чище и спокойнее для чтения.