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

In PostgreSQL a JSONB column stores structured data right inside a row, and most of the time you just need one scalar value out of it: an email from a profile, a plan from settings, an amount from order metadata. The ->> operator does exactly that, returning a field as plain text. Let's see how it differs from ->, how to cast the result to a number, and why it quietly returns NULL.

->> vs ->

Both operators fetch a value by key, but they return different types. -> gives you jsonb, while ->> gives you text. That is the key distinction.

-- payload is a jsonb column on orders
-- -> keeps jsonb, ->> returns plain text
SELECT
  payload -> 'channel'  AS as_jsonb,   -- "web"  (with quotes, type jsonb)
  payload ->> 'channel' AS as_text     -- web    (no quotes, type text)
FROM orders;

A simple rule of thumb:

• Use -> when you are drilling deeper and another -> or ->> will follow.
• Use ->> at the end of the chain, when you want a finished scalar value to print or compare.

For nested paths, combine them: descend with ->, then grab the leaf with ->>.

-- Nested: payload = {"shipping": {"city": "Berlin"}}
SELECT payload -> 'shipping' ->> 'city' AS city
FROM orders;

Casting the result to a type

->> always returns text, even when the JSON held a number or a boolean. To compare numerically or add it up, cast the result explicitly with ::.

-- (->> 'n')::int turns the text "3" into integer 3
SELECT
  id,
  (payload ->> 'items_count')::int   AS items,
  (payload ->> 'total')::numeric     AS total
FROM orders
WHERE (payload ->> 'total')::numeric > 100;

Gotcha: wrap ->> in parentheses before casting. Writing payload ->> 'total'::numeric is an error, because :: binds to the string literal 'total', not to the extracted value. The correct form is (payload ->> 'total')::numeric.

NULL when the key is missing

If the key is absent from the object, ->> does not raise an error; it returns NULL. That is convenient, but it sets a trap when filtering.

-- Missing key gives NULL, not an error
SELECT payload ->> 'coupon' AS coupon
FROM orders;   -- NULL where there is no "coupon"

Because of this, ->> alone cannot tell "key absent" apart from "key present but its value is JSON null" -- both yield SQL NULL. When that distinction matters, test for the key separately with the ? operator.

-- Distinguish a missing key from a present null
SELECT
  payload ? 'coupon'        AS has_key,     -- true / false
  payload ->> 'coupon'      AS coupon_text  -- text or NULL
FROM orders;

Filtering on JSON fields

The most common case is a WHERE on a field inside JSONB. Since ->> yields text, compare it either against a string or against a casted type.

-- String comparison: no cast needed
SELECT id, user_id
FROM orders
WHERE payload ->> 'channel' = 'mobile';

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

Handy tricks when filtering:

• Cast inside aggregates too: SUM((payload ->> 'total')::numeric).
• To skip rows without the key, add payload ? 'channel' or an IS NOT NULL condition.
• For a frequent field filter, build an expression index: CREATE INDEX ON orders ((payload ->> 'channel')).

-- Average JSON total per channel, ignoring rows without the field
SELECT
  payload ->> 'channel'           AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total
FROM orders
WHERE payload ? 'total'
GROUP BY payload ->> 'channel';

MySQL and ClickHouse

Other databases use different syntax, though the idea is the same.

• MySQL: it also has a ->> operator that returns unquoted text; it is sugar over JSON_UNQUOTE(JSON_EXTRACT(...)). The -> operator returns the value with quotes. The path is written as an expression like '$.channel'.
• ClickHouse: there are no ->/->> operators; use functions like JSONExtractString(payload, 'channel') for text and JSONExtractInt(...) for numbers; they return the typed value directly.

-- MySQL: ->> unquotes to plain text
SELECT payload ->> '$.channel' AS channel
FROM orders;

-- ClickHouse: typed extractors instead of operators
SELECT JSONExtractString(payload, 'channel') AS channel
FROM orders;

Bottom line: ->> is your go-to when you need a scalar out of JSONB to print or compare; keep -> for navigating nested structure, always wrap ->> in parentheses before ::, and remember that a missing key yields NULL.