JSONB Field Extraction in SQL

The -> and ->> operators pull values out of JSONB columns — one field at a time, one row at a time. They're what you use when a column stores semi-structured data and you need to work with the values inside it like regular SQL columns.

The difference between the two comes down to what they return. -> returns the extracted value as JSONB, preserving its structure so you can chain more extractions onto it. ->> returns the extracted value as plain text — ready for display or comparison, but all type information is gone.

The ->>'color' operator extracts color and returns it as plain text. The (attributes->>'weight_kg')::numeric extracts weight_kg as text and casts it to a number — the cast is what lets you compare it against numeric thresholds. Each ->> gives you the terminal value as text; when the value is a number or date, you always need an explicit cast to work with it.

When the JSONB has nested objects (like {'address': {'city': 'London'}}), use -> to navigate into the nested level as JSONB, then ->> for the final field:

Array elements use the same operators with integer indexes: -> 0 returns the first element as JSONB, ->> 0 returns it as text.

JSONB columns show up often in practice: an events table with a properties column storing arbitrary attributes, a products table with a metadata field for flexible specs, or a log table storing raw API payloads. Each row can have a different set of keys. You can't reference these fields with a regular column name — the extraction operators are how you get to them.

The one thing that trips people up

->> always returns text. If the JSONB field contains a number, ->> returns the string '80', not the number 80. Comparing or doing arithmetic with it requires an explicit cast:

The status comparison works without a cast because the target is already text. The score comparison needs ::numeric because you're comparing against a number. Skip the cast and PostgreSQL will raise a type error.

Navigating into a missing key returns NULL rather than an error, so a field that doesn't exist in one row's JSON silently becomes NULL in the output — which is usually correct for heterogeneous data, but worth knowing if you're assuming the key is always present.

For queries that filter on JSONB fields frequently, be aware that extraction in WHERE runs a full table scan unless there's a GIN index on the column. The query is correct either way, but performance can suffer on large tables without an index.

Deep paths with `#>` and `#>>`

For deeply nested structures, PostgreSQL also supports path-based operators. #> takes an array of keys as the path: metadata #> '{address, city}' is equivalent to metadata -> 'address' -> 'city'. #>> returns the same path as text, like ->>. These are useful when the path is longer or when you want to express nested navigation in a single operator. For one or two levels of nesting, chaining -> and ->> is clearer. For three or more levels, the path operators are easier to read.