Extracting JSONB as Text: the ->> Operator in PostgreSQL

В PostgreSQL тип jsonb позволяет хранить структурированные данные прямо в колонке.

Например, в заказе может лежать поле payload:

{
  "channel": "mobile",
  "total": 1500,
  "currency": "USD",
  "customer": {
    "email": "anna@mail.com",
    "country": "Vietnam"
  }
}

Это удобно, когда часть данных приходит из API, внешней системы, формы, webhook или события.

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

• канал заказа;
• e-mail пользователя;
• тариф из настроек;
• сумму из метаданных;
• город доставки;
• флаг включённой функции;
• id внешней системы.

В PostgreSQL для этого часто используют оператор:

->>

Он достаёт поле из jsonb и возвращает его как обычный text.

Например:

payload ->> 'channel'

вернёт:

mobile

А не JSON-строку с кавычками.

Разберём, чем ->> отличается от ->, как доставать вложенные поля, как приводить результат к числу и почему отсутствующий ключ молча даёт NULL.

Что делает оператор ->>

Оператор ->> достаёт значение из JSON/JSONB по ключу и возвращает его как текст.

Допустим, есть таблица orders:

id | payload
---+--------------------------------------------------------------
1  | {"channel": "mobile", "total": 1500, "currency": "USD"}
2  | {"channel": "web", "total": 2300, "currency": "EUR"}

Запрос:

SELECT
  id,
  payload ->> 'channel' AS channel
FROM orders;

Результат:

id | channel
---+---------
1  | mobile
2  | web

Значение channel достали из JSONB и получили обычный текст.

Это значит, что с ним можно работать как с текстовой колонкой:

WHERE payload ->> 'channel' = 'mobile'

или:

GROUP BY payload ->> 'channel'

-> и ->>: главная разница

В PostgreSQL есть два похожих оператора:

->
->>

Они оба достают значение из JSON, но возвращают разные типы.

Оператор -> возвращает значение как jsonb.

Оператор ->> возвращает значение как text.

Пример:

SELECT
  payload -> 'channel'  AS as_jsonb,
  payload ->> 'channel' AS as_text
FROM orders;

Если в payload лежит:

{"channel": "mobile"}

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

as_jsonb | as_text
---------+--------
"mobile" | mobile

В колонке as_jsonb значение осталось JSONB-значением, поэтому строка отображается с кавычками.

В колонке as_text значение стало обычным текстом, поэтому кавычек уже нет.

Можно запомнить так:

->  возвращает JSONB
->> возвращает text

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

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

-> используем, когда хотим продолжить идти внутрь JSON. ->> используем в конце, когда хотим получить готовое значение.

Например, есть JSON:

{
  "customer": {
    "email": "anna@mail.com",
    "country": "Vietnam"
  }
}

Чтобы достать email, сначала нужно войти в объект customer, а потом взять поле email как текст.

SELECT
  payload -> 'customer' ->> 'email' AS email
FROM orders;

Здесь:

payload -> 'customer'

возвращает вложенный JSONB-объект:

{"email": "anna@mail.com", "country": "Vietnam"}

А затем:

->> 'email'

достаёт из него текст:

anna@mail.com

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

payload ->> 'customer'

вы получите весь объект customer как текстовую строку. Это иногда нужно, но для дальнейшей навигации по JSON уже неудобно.

Вложенные поля через цепочку операторов

Для вложенных JSON-структур операторы можно соединять в цепочку.

Пример JSON:

{
  "shipping": {
    "address": {
      "city": "Berlin",
      "zip": "10115"
    }
  }
}

Чтобы достать город:

SELECT
  payload -> 'shipping' -> 'address' ->> 'city' AS city
FROM orders;

Логика такая:

payload
  -> shipping
    -> address
      ->> city

То есть:

payload -> 'shipping'

возвращает JSONB.

-> 'address'

тоже возвращает JSONB.

->> 'city'

возвращает финальное значение как текст.

Результат:

city
------
Berlin

Альтернатива для вложенных путей: #>>

Для вложенных полей в PostgreSQL есть ещё оператор:

#>>

Он достаёт значение по пути и сразу возвращает текст.

Например:

SELECT
  payload #>> '{shipping,address,city}' AS city
FROM orders;

Это эквивалентно:

SELECT
  payload -> 'shipping' -> 'address' ->> 'city' AS city
FROM orders;

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

Сравните:

payload -> 'shipping' -> 'address' -> 'geo' ->> 'lat'

и:

payload #>> '{shipping,address,geo,lat}'

Оба варианта рабочие.

Для новичка часто понятнее цепочка -> и ->>, потому что видно каждый шаг. Для короткой и аккуратной записи вложенного пути удобно использовать #>>.

Доступ к элементам массива

Операторы -> и ->> можно использовать не только с объектами, но и с массивами.

Допустим, в JSON лежит массив тегов:

{
  "tags": ["sql", "postgresql", "jsonb"]
}

Чтобы получить первый тег:

SELECT
  payload -> 'tags' ->> 0 AS first_tag
FROM articles;

Результат:

first_tag
---------
sql

Важно: индексация JSON-массивов начинается с 0.

0 -> sql
1 -> postgresql
2 -> jsonb

Если нужен второй тег:

SELECT
  payload -> 'tags' ->> 1 AS second_tag
FROM articles;

А если нужно достать весь массив как JSONB:

SELECT
  payload -> 'tags' AS tags_jsonb
FROM articles;

->> всегда возвращает text

Очень важный момент: ->> всегда возвращает текст.

Даже если в JSON лежит число.

Например:

{
  "items_count": 3,
  "total": 1500.50,
  "is_paid": true
}

Запрос:

SELECT
  payload ->> 'items_count' AS items_count,
  payload ->> 'total' AS total,
  payload ->> 'is_paid' AS is_paid
FROM orders;

вернёт текстовые значения:

items_count | total   | is_paid
------------+---------+--------
3           | 1500.50 | true

Выглядит как число и boolean, но тип результата — text.

Это значит, что для числовых сравнений, сумм и дат нужно явно приводить тип.

Приведение результата к числу

Если вы хотите сравнивать значение как число, нужно привести результат ->> к нужному типу.

Например:

SELECT
  id,
  (payload ->> 'items_count')::int AS items_count,
  (payload ->> 'total')::numeric AS total
FROM orders;

Обратите внимание на скобки:

(payload ->> 'total')::numeric

Это правильная запись.

Плохая запись:

payload ->> 'total'::numeric

Почему плохая?

Потому что PostgreSQL попробует привести строковый литерал 'total' к numeric, а не результат извлечения из JSON.

То есть ::numeric привяжется не туда.

Правило:

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

Правильно:

(payload ->> 'total')::numeric

(payload ->> 'items_count')::int

(payload ->> 'created_at')::timestamp

(payload ->> 'is_paid')::boolean

Фильтрация по числу из JSONB

Допустим, нужно найти заказы, у которых total больше 1000.

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

SELECT id
FROM orders
WHERE payload ->> 'total' > '1000';

это будет текстовое сравнение, а не числовое.

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

Для чисел нужно привести тип:

SELECT id
FROM orders
WHERE (payload ->> 'total')::numeric > 1000;

Теперь PostgreSQL сравнивает значения как числа.

То же самое для сортировки:

SELECT
  id,
  (payload ->> 'total')::numeric AS total
FROM orders
ORDER BY (payload ->> 'total')::numeric DESC;

Если не привести тип, сортировка будет текстовой.

Например, текстовая сортировка может поставить 900 выше 10000, потому что сравниваются символы, а не числа.

Фильтрация по строке из JSONB

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

Например, канал заказа:

{
  "channel": "mobile"
}

Фильтр:

SELECT
  id,
  user_id
FROM orders
WHERE payload ->> 'channel' = 'mobile';

Группировка:

SELECT
  payload ->> 'channel' AS channel,
  COUNT(*) AS orders_count
FROM orders
GROUP BY payload ->> 'channel';

Результат:

channel | orders_count
--------+-------------
mobile  | 120
web     | 95
partner | 30

Для текстовых значений ->> как раз очень удобен: он сразу даёт обычный text.

Агрегация чисел из JSONB

Если в JSONB лежит сумма заказа, её можно агрегировать.

Например:

{
  "channel": "mobile",
  "total": 1500.50
}

Посчитаем средний total по каналам:

SELECT
  payload ->> 'channel' AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total
FROM orders
WHERE payload ? 'total'
GROUP BY payload ->> 'channel';

Здесь:

payload ->> 'channel'

возвращает канал как текст.

А:

(payload ->> 'total')::numeric

достаёт сумму как текст и приводит её к числу.

Без приведения к numeric агрегат AVG не сможет корректно считать среднее по текстовым значениям.

Что будет, если ключа нет

Если ключа в JSONB нет, оператор ->> не падает с ошибкой.

Он возвращает SQL NULL.

Например, есть данные:

{"channel": "mobile", "total": 1500}

А мы пытаемся достать coupon:

SELECT
  payload ->> 'coupon' AS coupon
FROM orders;

Результат:

coupon
------
NULL

Это удобно: запрос не ломается из-за отсутствующего ключа.

Но это может быть ловушкой.

Например:

WHERE payload ->> 'coupon' = 'SUMMER'

не вернёт строки без coupon, потому что сравнение NULL = 'SUMMER' не даёт TRUE.

Если нужно найти строки без купона:

SELECT id
FROM orders
WHERE payload ->> 'coupon' IS NULL;

Но здесь есть ещё одна тонкость: NULL может означать разные вещи.

Отсутствующий ключ и JSON null

Допустим, есть два JSON-документа:

{"channel": "mobile"}

и:

{"channel": "mobile", "coupon": null}

В первом случае ключа coupon вообще нет.

Во втором случае ключ есть, но его значение — JSON null.

Если использовать ->>:

SELECT
  payload ->> 'coupon' AS coupon
FROM orders;

оба случая дадут SQL NULL.

То есть только по ->> нельзя понять:

• ключ отсутствует;
• ключ есть, но его значение null.

Если это важно, проверяйте наличие ключа отдельно через оператор ?.

SELECT
  payload ? 'coupon' AS has_coupon_key,
  payload ->> 'coupon' AS coupon_text
FROM orders;

Результат может быть таким:

has_coupon_key | coupon_text
---------------+------------
false          | NULL
true           | NULL
true           | SUMMER

Теперь видно:

• has_coupon_key = false — ключа нет;
• has_coupon_key = true, но coupon_text IS NULL — ключ есть, но значение null;
• coupon_text = 'SUMMER' — ключ есть и значение текстовое.

Важно: оператор ? проверяет наличие ключа на верхнем уровне объекта.

Для вложенного объекта нужно сначала перейти внутрь:

(payload -> 'customer') ? 'email'

Как отфильтровать строки, где ключ есть

Если нужно выбрать только строки, где в JSON есть ключ channel, используйте:

SELECT id
FROM orders
WHERE payload ? 'channel';

Если нужно выбрать строки, где ключа нет:

SELECT id
FROM orders
WHERE NOT (payload ? 'channel');

Если нужно выбрать строки, где ключ есть и значение не NULL:

SELECT id
FROM orders
WHERE payload ? 'channel'
  AND payload ->> 'channel' IS NOT NULL;

Для вложенных ключей:

SELECT id
FROM orders
WHERE (payload -> 'customer') ? 'email';

Но если customer может отсутствовать или быть не объектом, такие запросы нужно тестировать аккуратно.

Грязные данные и ошибки приведения типов

->> возвращает текст.

Если вы приводите этот текст к числу, PostgreSQL попытается выполнить cast.

Например:

(payload ->> 'total')::numeric

Это работает, если значение выглядит как число:

{"total": 1500}

или:

{"total": "1500.50"}

Но если в JSON внезапно лежит пустая строка или текст:

{"total": ""}

или:

{"total": "unknown"}

запрос упадёт с ошибкой.

Например:

SELECT
  (payload ->> 'total')::numeric AS total
FROM orders;

может сломаться на одной грязной строке.

Для данных из внешних API это частая проблема.

Как безопаснее приводить JSON-значение к числу

Если данные могут быть грязными, сначала проверьте формат.

Например:

SELECT
  id,
  CASE
    WHEN payload ->> 'total' ~ '^[0-9]+(\.[0-9]+)?$'
      THEN (payload ->> 'total')::numeric
    ELSE NULL
  END AS total
FROM orders;

Этот пример пропускает только положительные числа вроде:

100
1500.50
0.99

Если вам нужны отрицательные числа, можно расширить регулярное выражение:

CASE
  WHEN payload ->> 'total' ~ '^-?[0-9]+(\.[0-9]+)?$'
    THEN (payload ->> 'total')::numeric
  ELSE NULL
END

Ещё один частый приём — обработать пустую строку через NULLIF:

(NULLIF(payload ->> 'total', ''))::numeric

Но это спасает только от пустой строки. Если там будет unknown, cast всё равно упадёт.

Поэтому для внешних данных лучше:

• валидировать JSON при записи;
• нормализовать важные поля в отдельные typed-колонки;
• использовать проверки перед cast;
• не строить финансовые отчёты напрямую по грязному JSON без контроля качества данных.

Индекс для частого фильтра по JSON-полю

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

Например, часто есть запрос:

SELECT id
FROM orders
WHERE payload ->> 'channel' = 'mobile';

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

CREATE INDEX orders_payload_channel_idx
ON orders ((payload ->> 'channel'));

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

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

CREATE INDEX orders_payload_total_idx
ON orders (((payload ->> 'total')::numeric));

Но здесь нужно быть осторожным: если в данных есть нечисловые значения, создание такого индекса может упасть из-за ошибки приведения.

Для важных полей иногда лучше вынести значение из JSONB в отдельную колонку:

orders.total numeric
orders.channel text

А JSONB оставить для дополнительных метаданных.

JSONB как метаданные, а не замена модели данных

jsonb удобен, но не стоит превращать его в склад всех важных полей.

Если поле активно используется в:

• фильтрах;
• JOIN;
• сортировках;
• агрегатах;
• ограничениях;
• индексах;
• отчётах;
• бизнес-логике,

часто лучше хранить его в обычной колонке правильного типа.

Например, если total — ключевая сумма заказа, лучше иметь:

orders.amount numeric

А не только:

{"total": 1500}

Почему?

Потому что обычную колонку проще:

• индексировать;
• валидировать;
• агрегировать;
• сравнивать;
• защищать constraints;
• использовать в JOIN;
• документировать в схеме.

Хороший паттерн:

Важные стабильные поля — обычными колонками. Гибкие дополнительные данные — в JSONB.

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

Пример: e-mail из профиля пользователя

Допустим, в таблице users есть колонка profile jsonb.

{
  "email": "anna@mail.com",
  "name": "Anna",
  "settings": {
    "language": "ru",
    "timezone": "Asia/Singapore"
  }
}

Достаём e-mail и язык:

SELECT
  id,
  profile ->> 'email' AS email,
  profile -> 'settings' ->> 'language' AS language
FROM users;

Результат:

id | email         | language
---+---------------+---------
1  | anna@mail.com | ru

Если нужно отфильтровать русскоязычных пользователей:

SELECT id
FROM users
WHERE profile -> 'settings' ->> 'language' = 'ru';

Или через #>>:

SELECT id
FROM users
WHERE profile #>> '{settings,language}' = 'ru';

Пример: тариф из настроек

Допустим, есть таблица accounts, а в settings лежит тариф:

{
  "billing": {
    "plan": "pro",
    "seats": 5
  }
}

Запрос:

SELECT
  id,
  settings -> 'billing' ->> 'plan' AS plan,
  (settings -> 'billing' ->> 'seats')::int AS seats
FROM accounts;

Фильтр по тарифу:

SELECT id
FROM accounts
WHERE settings -> 'billing' ->> 'plan' = 'pro';

Фильтр по количеству мест:

SELECT id
FROM accounts
WHERE (settings -> 'billing' ->> 'seats')::int >= 5;

Пример: средний чек по каналу из JSONB

Допустим, в orders.payload лежит:

{
  "channel": "mobile",
  "total": 1500.50
}

Нужно посчитать средний чек по каналам.

SELECT
  payload ->> 'channel' AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total,
  COUNT(*) AS orders_count
FROM orders
WHERE payload ? 'channel'
  AND payload ? 'total'
GROUP BY payload ->> 'channel'
ORDER BY avg_total DESC;

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

SELECT
  payload ->> 'channel' AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total,
  COUNT(*) AS orders_count
FROM orders
WHERE payload ? 'channel'
  AND payload ->> 'total' ~ '^-?[0-9]+(\.[0-9]+)?$'
GROUP BY payload ->> 'channel'
ORDER BY avg_total DESC;

Так запрос не упадёт, если где-то в total лежит не число.

->> и LIKE / ILIKE

Так как ->> возвращает текст, его можно использовать с текстовыми операторами.

Например, найти пользователей с e-mail на Gmail:

SELECT id
FROM users
WHERE profile ->> 'email' ILIKE '%@gmail.com';

Или найти заказы, где канал начинается с partner_:

SELECT id
FROM orders
WHERE payload ->> 'channel' LIKE 'partner_%';

Но помните: поиск по подстроке через ILIKE '%text%' может быть дорогим на больших таблицах. Для частых поисков нужны подходящие индексы и отдельная стратегия.

->> и COALESCE

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

Например:

SELECT
  id,
  COALESCE(payload ->> 'channel', 'unknown') AS channel
FROM orders;

Если channel отсутствует или равен JSON null, результат будет:

unknown

Это удобно для отчётов:

SELECT
  COALESCE(payload ->> 'channel', 'unknown') AS channel,
  COUNT(*) AS orders_count
FROM orders
GROUP BY COALESCE(payload ->> 'channel', 'unknown');

Но используйте COALESCE осознанно. Иногда отсутствие значения и явное значение "unknown" должны различаться.

MySQL: похожий оператор ->>

В MySQL тоже есть оператор ->> для JSON.

Но путь задаётся иначе — через JSONPath.

PostgreSQL:

SELECT
  payload ->> 'channel' AS channel
FROM orders;

MySQL:

SELECT
  payload ->> '$.channel' AS channel
FROM orders;

В MySQL оператор ->> по смыслу похож на:

JSON_UNQUOTE(JSON_EXTRACT(payload, '$.channel'))

То есть он достаёт значение из JSON и убирает JSON-кавычки.

Для вложенного поля:

SELECT
  payload ->> '$.customer.email' AS email
FROM orders;

Идея похожая, но синтаксис путей отличается от PostgreSQL.

ClickHouse: функции вместо ->>

В ClickHouse обычно используют функции извлечения из JSON.

Например, для строки:

SELECT
  JSONExtractString(payload, 'channel') AS channel
FROM orders;

Для числа:

SELECT
  JSONExtractFloat(payload, 'total') AS total
FROM orders;

или:

SELECT
  JSONExtractInt(payload, 'items_count') AS items_count
FROM orders;

То есть в PostgreSQL часто пишут:

payload ->> 'channel'

а в ClickHouse:

JSONExtractString(payload, 'channel')

Разница в том, что ClickHouse-функции часто сразу возвращают нужный тип: строку, число, boolean и так далее.

Практические шаблоны

Достать поле как текст

SELECT
  id,
  payload ->> 'channel' AS channel
FROM orders;

Достать вложенное поле

SELECT
  id,
  payload -> 'customer' ->> 'email' AS email
FROM orders;

Достать вложенное поле через #>>

SELECT
  id,
  payload #>> '{customer,email}' AS email
FROM orders;

Достать число и привести тип

SELECT
  id,
  (payload ->> 'total')::numeric AS total
FROM orders;

Фильтр по строковому JSON-полю

SELECT
  id
FROM orders
WHERE payload ->> 'channel' = 'mobile';

Фильтр по числовому JSON-полю

SELECT
  id
FROM orders
WHERE (payload ->> 'priority')::int >= 5;

Проверить наличие ключа

SELECT
  id,
  payload ? 'coupon' AS has_coupon
FROM orders;

Отличить отсутствующий ключ от JSON null

SELECT
  id,
  payload ? 'coupon' AS has_coupon_key,
  payload ->> 'coupon' AS coupon_text
FROM orders;

Подставить значение по умолчанию

SELECT
  id,
  COALESCE(payload ->> 'channel', 'unknown') AS channel
FROM orders;

Безопаснее привести число после проверки

SELECT
  id,
  CASE
    WHEN payload ->> 'total' ~ '^-?[0-9]+(\.[0-9]+)?$'
      THEN (payload ->> 'total')::numeric
    ELSE NULL
  END AS total
FROM orders;

Среднее значение из JSONB по группам

SELECT
  payload ->> 'channel' AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total
FROM orders
WHERE payload ->> 'total' ~ '^-?[0-9]+(\.[0-9]+)?$'
GROUP BY payload ->> 'channel';

Индекс по JSONB-полю как тексту

CREATE INDEX orders_payload_channel_idx
ON orders ((payload ->> 'channel'));

Индекс по JSONB-полю как числу

CREATE INDEX orders_payload_total_idx
ON orders (((payload ->> 'total')::numeric));

Частые ошибки

Перепутали -> и ->>

Если нужно сравнить со строкой, обычно нужен ->>:

WHERE payload ->> 'channel' = 'mobile'

А не:

WHERE payload -> 'channel' = 'mobile'

payload -> 'channel' возвращает JSONB, а 'mobile' — текстовый литерал.

Забыли скобки перед приведением типа

Плохо:

payload ->> 'total'::numeric

Правильно:

(payload ->> 'total')::numeric

Сравнили число как текст

Плохо:

WHERE payload ->> 'total' > '1000'

Лучше:

WHERE (payload ->> 'total')::numeric > 1000

Не учли отсутствующий ключ

payload ->> 'coupon'

вернёт NULL, если ключа нет.

Если важно именно наличие ключа:

payload ? 'coupon'

Не проверили грязные данные перед cast

Такой запрос может упасть:

(payload ->> 'total')::numeric

если в JSON лежит:

{"total": "unknown"}

Для внешних данных лучше валидировать формат.

Что важно запомнить

Оператор ->> достаёт значение из JSONB и возвращает его как text.

Пример:

SELECT
  payload ->> 'channel' AS channel
FROM orders;

Главные правила:

• -> возвращает jsonb;
• ->> возвращает text;
• -> удобно использовать для движения внутрь JSON;
• ->> обычно используют в конце цепочки;
• для вложенных полей можно писать цепочку -> ... ->>;
• для вложенного пути можно использовать #>>;
• результат ->> всегда текст, даже если в JSON лежит число;
• для числовых сравнений нужен явный cast;
• перед cast используйте скобки: (payload ->> 'total')::numeric;
• отсутствующий ключ возвращает NULL;
• JSON null через ->> тоже превращается в SQL NULL;
• чтобы проверить наличие ключа, используйте оператор ?;
• под частые фильтры можно создавать выражающие индексы.

Короткий вывод

->> — один из самых полезных операторов PostgreSQL для работы с jsonb.

Он нужен, когда вы хотите достать из JSONB обычное скалярное значение и работать с ним как с текстом.

Например:

payload ->> 'channel'

достаёт канал заказа как текст.

Для вложенных полей:

payload -> 'customer' ->> 'email'

или:

payload #>> '{customer,email}'

Главная мысль:

-> оставляет значение в JSONB, ->> достаёт финальное значение как text.

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

(payload ->> 'total')::numeric

И всегда помните: если ключа нет, ->> не упадёт, а вернёт NULL. Это удобно для гибких данных, но требует аккуратности в фильтрах, отчётах и приведении типов.