Extracting Nested JSONB Values With Path Operators

Once a document is two or three levels deep, a chain of arrows becomes hard to read, and the day you have to reach into an array of objects the arrows give up. PostgreSQL gives you two answers: the path operators #> and #>>, which are the arrows with a path in one step, and the jsonpath language, which can filter, compare and walk a document in a way the operators cannot. This article covers both with the outputs from a run, including the places where the behavior surprised me.

The data

Three orders, kept small so every result is easy to check by eye:

create table orders (id int primary key, doc jsonb not null);
insert into orders values
(1, '{"customer": {"id": 10, "name": "Ada"}, "items": [{"sku": "A1", "qty": 2, "price": 9.5}, {"sku": "B2", "qty": 1, "price": 20}], "tags": ["gift", "rush"]}'),
(2, '{"customer": {"id": 11, "name": "Grace"}, "items": [{"sku": "A1", "qty": 5, "price": 9.5}], "tags": []}'),
(3, '{"customer": {"id": 10, "name": "Ada"}, "items": [], "note": null}');

The path operators

#> takes a text array as the path and returns jsonb. #>> takes the same path and returns text:

select doc #>  '{customer}'      as customer,
       doc #>> '{items,0,sku}'   as first_sku,
       doc #>> '{items,1,price}' as second_price,
       doc #>> '{items,-1,sku}'  as last_sku
from orders where id = 1;

          customer          | first_sku | second_price | last_sku 
----------------------------+-----------+--------------+----------
 {"id": 10, "name": "Ada"}  | A1        | 20           | B2

Each step is a key for an object or an integer for an array, and the integers are written the same way as the keys, inside the braces. A negative integer works as it does for the arrow operator and counts from the end, so -1 found the last item without knowing how many there are. The path is a real text array, so you can build it. doc #>> array['customer','name'] returned Ada, and doc #>> string_to_array('customer.id', '.') returned 10, which is a handy way to accept a dotted path from a caller (as long as you pass it as a parameter, not by pasting text into the query).

Three edge cases are worth knowing:

• A path that does not exist returns SQL NULL and no error. doc #>> '{customer,name,first}' returned NULL because name is a string, not an object. A typo in a key does the same, and nothing warns you.

• An empty path, '{}', returns the whole document, which is occasionally useful in generic code.

• An index past the end of an array returns NULL: doc #> '{items,9}' was empty.

The operators are equivalent to chains of ->. doc #>> '{items,0,sku}' is doc -> 'items' -> 0 ->> 'sku'. The advantage of the path form is that the path can be data, and the long chain is shorter to write. The advantage of the chain is that it reads in the same direction as the document. Use whichever your team can read at a glance, and see querying JSONB with the arrow operators for the traps of casts and precedence, which apply here too: (doc #>> '{items,0,qty}')::int, with the parentheses.

jsonpath for conditions

The path operators can only follow one fixed route. A jsonpath expression can describe a route, select many values, test a condition and apply a method. The language follows the SQL/JSON standard, and PostgreSQL exposes it through functions:

select jsonb_path_query(doc, '$.items[*].sku') from orders where id = 1;

returned "A1" and "B2" as two rows. In the path, $ is the document, .items is a key, and [*] means every element of the array. The function jsonb_path_query returns a set of values, one row each. Its relatives are jsonb_path_query_array, which collects them into one array ([2, 1] for the quantities of order 1), and jsonb_path_query_first, which returns the first match.

Filters

A filter goes after a ? in parentheses, and @ is the current element:

select jsonb_path_query(doc, '$.items[*] ? (@.price > 10)') from orders where id = 1;

That returned {"qty": 1, "sku": "B2", "price": 20}. Filters can combine tests with && and ||, compare strings, and use starts with: $.items[*] ? (@.sku starts with "A") returned the A1 item. Variables keep values out of the path text:

select jsonb_path_query(doc, '$.items[*] ? (@.price > $min).sku', '{"min": 9}')
from orders where id = 1;

Both skus came back, because both prices exceed 9. Pass values as variables instead of building the expression by string concatenation, for the same reason you use bind parameters in SQL.

Methods

Methods at the end of an expression compute something about a value. $.items.size() returned 2, 1 and 0 for the three orders, which is how you count array elements without unnesting. $.items[last].sku returned B2 and A1 for the first two orders and nothing for the third, since its array is empty. .type() returned "number" for each price, and .datetime() parsed "2026-10-01" into a date value (printed as the string "2026-10-01"). Arithmetic works on a single number but not on a list: $.items[*].qty * 2 raised left operand of jsonpath operator * is not a single numeric value.

Using a path as a condition

Two operators turn a path into a boolean for WHERE. @? asks whether the path returns anything, and @@ evaluates a predicate:

select id from orders where doc @? '$.items[*] ? (@.qty >= 5)';   -- 2
select id from orders where doc @? '$.note';                       -- 3
select id from orders where doc @@ '$.items.size() == 0';          -- 3

The second one is worth a look. Order 3 has "note": null, and @? '$.note' matched it, because the key exists, even though its value is a JSON null. The question @? answers is "does this path produce a value", and a JSON null is a value.

These operators are also the ones a GIN index can serve, as explained in indexing JSONB with GIN and when it helps. jsonb_path_exists(doc, '...') gives the same answer as @?, but as a function call the planner cannot use an index with it, so it scans every row. In a WHERE clause on a big table, prefer the operator form.

Lax and strict

jsonpath has two modes. Lax is the default, and it forgives structural mismatches: a missing key produces no result and an array is automatically unwrapped where an object was expected. Strict raises an error for the same mismatches. I tried a key that does not exist:

jsonb_path_query(doc, 'lax $.customer.nothing')     (no rows)
jsonb_path_query(doc, 'strict $.customer.nothing')  ERROR: JSON object does not contain key "nothing"
jsonb_path_query(doc, 'strict $.customer.nothing', silent => true)   (no rows)

Strict mode with a loud error is what you want in a data pipeline where a missing field means the input is broken. The silent => true argument suppresses the error and makes the call behave like lax mode for that failure, which is useful inside a larger query that should not abort.

Lax mode has one surprise that I would not have guessed. The recursive wildcard $.** visits every nested value, and in lax mode the automatic array unwrapping makes it visit the elements twice. jsonb_path_query(doc, '$.**.sku') returned four rows for order 1 (A1, B2, A1, B2) and the right two once I put strict in front. If you use .**, use strict mode, and compare the count to what you expect.

Choosing between the two families

I use the operators when the path is fixed and known, and the value is wanted as a column: doc #>> '{customer,name}' in a select list is shorter and clearer than anything else. I use jsonpath when the question has a condition in it, or when the path goes through an array, or when several values should come out. A rough guide:

For arrays of objects, the next steps are querying arrays inside JSONB and expanding JSONB into rows with jsonb_to_recordset. The JSONPath tester on this site is a quick way to try an expression against a document before you put it in a query. Note that it follows the Goessner-style JSONPath, and PostgreSQL's jsonpath is a close cousin with its own syntax, for example ?(@.price > 10) in both, but strict, lax and the methods only in PostgreSQL. Test the final expression in psql.

When a path is the wrong tool

Paths are for reading, and they have limits.

Updating. The operators and the query functions only read. To change a value at a path, jsonb_set takes the same text-array path. See updating one key inside a JSONB column.

Filtering a large table by a path. jsonb_path_query and jsonb_path_exists in a WHERE clause have no index support. Use @?, @@ or @> with a GIN index, or index the expression you extract.

Dynamic paths from user input. A path in a jsonpath string is parsed by the server; a syntax error is an error, and a hostile string can be expensive (a recursive wildcard over a huge document). Treat a user-supplied path as a feature that needs limits, and pass values as variables.

Typed output. A path returns JSON. For typed columns from arrays of objects, jsonb_to_recordset or JSON_TABLE (using JSON_TABLE in PostgreSQL 17 to turn JSON into rows) assign types in one step.

The reference for the operators and the path language is in the PostgreSQL manual, under JSON functions and operators and the SQL/JSON path language.