JSONB Path Operators in PostgreSQL: #> and #>> for Nested Values

When a column holds JSONB and the value you need is buried three or four levels deep, a chain of -> quickly turns into an unreadable staircase. PostgreSQL gives you two path operators, #> and #>>, that fetch a value by path in a single expression. Let's see how they work and when to reach for jsonb_path_query instead.

What #> and #>> do

Both operators take a path as a text array text[] and walk it top to bottom. The only difference is the result type:

• #> returns jsonb (a nested object, array, or scalar as JSON).
• #>> returns text (the unwrapped scalar, with no quotes).

Say users has a profile jsonb column shaped like {"address": {"city": "Berlin", "geo": [52.5, 13.4]}, "tags": ["pro", "eu"]}.

-- jsonb result: still a JSON object
SELECT profile #> '{address}' AS address_json
FROM users
WHERE id = 1;

-- text result: the raw city string, no quotes
SELECT profile #>> '{address,city}' AS city
FROM users
WHERE id = 1;

The mnemonic is simple: the extra > squeezes the value down to text. For WHERE, JOIN, and comparisons you almost always want #>>, because you are matching against a plain string.

Shorter than the -> chain

The main win is that one path replaces several arrows. Compare two equivalent queries:

-- The arrow chain: noisy and easy to misread
SELECT profile -> 'address' ->> 'city' AS city
FROM users;

-- Same thing with a single path operator
SELECT profile #>> '{address,city}' AS city
FROM users;

The deeper the structure, the bigger the payoff. The path '{a,b,c,d}' reads as one unit, whereas -> 'a' -> 'b' -> 'c' ->> 'd' has to be parsed link by link.

Mixing object keys and array indexes

A path can interleave object keys and array indexes in the same string. Indexes are numbers written as text, zero-based, and negatives count from the end.

-- First geo coordinate (array index 0) inside the address object
SELECT profile #>> '{address,geo,0}' AS latitude
FROM users
WHERE id = 1;

-- Last tag using a negative index
SELECT profile #>> '{tags,-1}' AS last_tag
FROM users
WHERE id = 1;

A realistic example: orders has a meta jsonb with a list of line items, and you want the SKU of the first item for paid orders.

-- meta = {"items": [{"sku": "A-1", "qty": 2}, ...]}
SELECT o.id,
       o.meta #>> '{items,0,sku}' AS first_sku
FROM orders o
WHERE o.status = 'paid';

Gotcha: if any step of the path is missing, or you address a key as an index (or vice versa), the operator does not error out -- it quietly returns NULL. Convenient, but it masks typos in a key name: you cannot tell "no data" from "wrong path" by the result alone.

When to use jsonb_path_query

Path operators fetch exactly one value at a fixed path. The moment you need conditions, a scan over every array element, or wildcards, switch to jsonb_path_query and the JSONPath language (PostgreSQL 12+).

-- All SKUs across the whole items array, one row each
SELECT o.id, sku AS item_sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku;

-- Path with a filter: items where qty > 1
SELECT o.id,
       jsonb_path_query(o.meta, '$.items[*] ? (@.qty > 1)') AS big_item
FROM orders o;

Here jsonb_path_query is a set-returning function placed in FROM, so its alias sku is the value column itself -- you select the bare alias, not sku.value. Each match becomes its own row, unlike the scalar #> and #>> operators, which return a single value wherever you write them.

A quick rule of thumb:

• You know the exact path and want one value -- #> or #>>.
• You need filters, [*], or multiple matches -- jsonb_path_query.
• You only want a yes/no match in WHERE -- the @? operator with JSONPath is shorter and GIN-indexable.

A last word on performance: both #>> expressions and JSONPath can be sped up with an index -- an expression index (CREATE INDEX ON users ((profile #>> '{address,city}'))) for a specific path, or a GIN index with jsonb_path_ops for flexible queries.