JSONB Arrow Operators: -> and ->> in PostgreSQL

When you store semi-structured data in a JSONB column, every read starts with two operators: -> and ->>. They are the foundation of JSON querying in PostgreSQL: one drills deeper into the structure, the other pulls out a ready-to-use value. Let's see how they differ, how to reach object fields and array elements, and how to chain them.

-> returns jsonb, ->> returns text

Both operators fetch a value by key, but they return different types. -> gives you jsonb (an object, array, or scalar in JSON form), while ->> gives you plain text. That single distinction drives everything else.

-- prefs is a jsonb column on users: {"theme": "dark", "lang": "en"}
SELECT
  prefs -> 'theme'  AS as_jsonb,   -- "dark"  (with quotes, type jsonb)
  prefs ->> 'theme' AS as_text     -- dark    (no quotes, type text)
FROM users;

A simple rule of thumb:

• Use -> when you are going deeper and another -> or ->> will follow.
• Use ->> at the end of the path, when you want a scalar value to display or compare.

The key is a string (an object field name), and a missing key yields NULL rather than an error.

Object fields and array elements

If the right-hand side is a string, PostgreSQL looks up an object field. If it is an integer, it indexes into an array, and indexing is zero-based. A negative index counts from the end.

-- meta = {"tags": ["new", "vip", "eu"], "score": 7}
SELECT
  meta -> 'tags' -> 0     AS first_tag_json,   -- "new" as jsonb
  meta -> 'tags' ->> 0    AS first_tag_text,   -- new  as text
  meta -> 'tags' ->> -1   AS last_tag          -- eu   (last element)
FROM users;

The same works over your own tables. Say orders has a payload holding a line-item array:

-- payload = {"items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]}
SELECT
  id,
  payload -> 'items' -> 0 ->> 'sku' AS first_sku
FROM orders
WHERE status = 'paid';

Chaining and casting

A long path is just several operators in a row. Drill with ->, staying in jsonb, then switch to ->> on the last step to get text. Because ->> always returns text, cast explicitly with :: for numbers, dates, and booleans.

-- profile = {"address": {"city": "Berlin", "zip": "10115"}, "age": "34"}
SELECT
  profile -> 'address' ->> 'city'        AS city,
  (profile ->> 'age')::int               AS age_int
FROM users
WHERE (profile ->> 'age')::int >= 18;

A real aggregation: pull an amount out of JSON metadata and compute the average order value per country.

-- payload = {"amount": "149.90", "currency": "EUR"}
SELECT
  u.country,
  round(avg((o.payload ->> 'amount')::numeric), 2) AS avg_amount
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.country
ORDER BY avg_amount DESC;

Gotchas

• Comparing without a cast is almost always a bug: payload ->> 'amount' > '99' compares strings lexicographically, so '9' sorts above '100'. Cast to a number: (payload ->> 'amount')::numeric > 99.
• -> on a scalar yields jsonb, so prefs -> 'theme' = 'dark' fails with a type error; use ->> to compare, or write prefs -> 'theme' = '"dark"'::jsonb.
• A missing key or an out-of-range array index returns NULL silently, so a typo in a field name produces no error and quietly corrupts the result.
• ->> on an object or array returns its full JSON text, not a single value; only take text on scalars.

MySQL has no arrow chaining like this: use JSON_EXTRACT(doc, '$.address.city') or the shorthand doc->>'$.address.city' (its ->> also returns text). ClickHouse offers functions such as JSONExtractString(col, 'city') and JSONExtractInt(col, 'age'). The idea is universal, but the -> / ->> arrow syntax is a PostgreSQL feature.