JSONB Path queries: the syntax for what the basic operators don't express

Capsule overview

The @>, ?, and ->> operators solve 80% of the JSONB queries you'll write. The other 20% — the ones that show up when you need to "filter by elements of an array where a property meets a condition," or "extract all the nested values that match a predicate," or "verify that at least one object with these properties exists" — become unreadable or downright impossible with the basic operators. You end up with baroque SQL full of jsonb_array_elements + subqueries that nobody wants to maintain.

PostgreSQL 12 introduced JSON Path queries, a dedicated syntax inspired by XPath/JSONPath. It's to JSONB what regexes are to strings: a mini-DSL specific to describing complex patterns compactly. The learning curve is real (new syntax), but the productivity afterwards is enormous — queries that used to take 10 lines become 2.

This capsule teaches you the subset of JSON Path you'll actually use: navigation, filters, predicates, and the three operators that activate it in SQL (@@, @?, jsonb_path_query). You aren't going to learn the complete spec (it's extensive); you're going to learn the 80% that covers 95% of the cases. By the end you'll be able to recognize when a path query beats the basic operators, and you'll be able to write filters over arrays of objects without fighting jsonb_array_elements.

Mental model: JSON Path is a mini-DSL embedded in strings

JSON Path isn't SQL. It's its own mini-language (standardized in SQL/JSON) that you live inside a PostgreSQL string. The SQL query gives you the operator (@@, @?, jsonb_path_query) and you pass it a path expression as a string.

                    ┌──────────────────────┐
                    │  PostgreSQL SQL      │
                    │                      │
                    │  WHERE payload @@    │
                    │    '$.amount > 100'  │  ← string with JSON Path syntax
                    │                      │
                    └──────────────────────┘
                                            ↑
                                       JSON Path
                                       (mini-DSL)

JSON Path syntax starts with $ (the root document) and from there you navigate with dots, brackets, and filters. We're going to build it up from scratch.

Comparison: the same question, two ways

Question: "does the JSON have an element in tags equal to 'admin'?"

With basic operators:

SELECT * FROM users
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements_text(data->'tags') AS t
  WHERE t = 'admin'
);
-- Or more concise but with the array-specific operator:
SELECT * FROM users WHERE data->'tags' ? 'admin';

With JSON Path:

SELECT * FROM users WHERE data @? '$.tags[*] ? (@ == "admin")';

For this simple case, the basic operators are short and clear. But let's raise the level: "is there any purchase in the events array with amount > 100?":

With basic operators:

SELECT * FROM users
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(data->'events') AS e
  WHERE e->>'type' = 'purchase'
    AND (e->>'amount')::numeric > 100
);

With JSON Path:

SELECT * FROM users
WHERE data @? '$.events[*] ? (@.type == "purchase" && @.amount > 100)';

Here the path query starts to win. More expressive, a single line, no subquery.

The three operators that activate JSON Path

There are variants (jsonb_path_query_first, jsonb_path_query_array, jsonb_path_exists, jsonb_path_match) that are convenience wrappers. We're going to stick with the three main ones and mention the useful ones as they come up.

Key difference: @@ vs @?

This is confusing at first. Summary:

• @@ (matches): the path expression must be a predicate (something that returns a boolean) and the operator returns whether the predicate is true.
• @? (exists): the path expression can return anything, and the operator returns whether there's at least one result.

-- @@: the path is a complete predicate
SELECT '{"a": 5}'::jsonb @@ '$.a > 3';
-- true

-- @?: the path doesn't have to be a predicate, it's enough that it matches something
SELECT '{"a": 5}'::jsonb @? '$.a';
-- true (the key 'a' exists)

SELECT '{"a": 5}'::jsonb @? '$.b';
-- false (the key 'b' doesn't exist)

When the path has a filter at the end (? (@ == ...)), both @@ and @? work; but the convention is @? for "something that matches exists."

JSON Path syntax: the essentials

Navigation

$              ← the root of the document
$.field        ← key access
$.a.b.c        ← nested access
$.array[*]     ← every element of the array (wildcard)
$.array[0]     ← first element
$.array[0,2]   ← elements 0 and 2
$.array[1 to 3] ← elements 1 through 3
$.*            ← any key of the root object
$.**           ← recursive descent (any level) — useful but expensive

Filters

?  (predicate)    ← filters the elements where the predicate is true
@                 ← the "current" element inside the filter

-- "every item where @ (the current item) > 10"
$.items[*] ? (@ > 10)

-- "every product where the price > 100"
$.products[*] ? (@.price > 100)

Predicate operators

Strings, numbers, booleans

"text"         ← strings with double quotes
42, 3.14       ← numbers
true, false    ← booleans
null           ← JSON null

Common trap: the quotes are double inside the path expression, even though the path expression lives inside a SQL string with single quotes. The trick is:

SELECT data @@ '$.country == "US"' FROM events;
-- ✅ Single quotes for the SQL string, double ones inside the path

Progressive examples

Setup

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('{
  "name": "Ana",
  "age": 30,
  "country": "AR",
  "tags": ["admin", "active"],
  "events": [
    {"type": "login", "ts": "2026-04-01"},
    {"type": "purchase", "amount": 150, "ts": "2026-04-15"}
  ]
}'),
('{
  "name": "Bob",
  "age": 22,
  "country": "US",
  "tags": ["active"],
  "events": [
    {"type": "view", "ts": "2026-04-20"},
    {"type": "purchase", "amount": 50, "ts": "2026-04-25"}
  ]
}'),
('{
  "name": "Carla",
  "age": 45,
  "country": "MX",
  "tags": ["editor"],
  "events": [
    {"type": "purchase", "amount": 300, "ts": "2026-04-10"},
    {"type": "purchase", "amount": 200, "ts": "2026-04-22"}
  ]
}');

Example 1: a simple filter at the root

"Users older than 25."

SELECT data->>'name'
FROM users
WHERE data @@ '$.age > 25';
-- Output: Ana, Carla

Equivalent with basic operators:

SELECT data->>'name' FROM users WHERE (data->>'age')::int > 25;

For this case, the basic operators are as clear as JSON Path. The choice is stylistic.

Example 2: existence in an array

"Users that have the 'admin' tag."

SELECT data->>'name'
FROM users
WHERE data @? '$.tags[*] ? (@ == "admin")';
-- Output: Ana

Equivalent with ? (the string-existence-in-array operator):

SELECT data->>'name' FROM users WHERE data->'tags' ? 'admin';

For arrays of strings, the ? operator is cleaner. JSON Path starts to shine with arrays of objects.

Example 3: a filter on an array of objects

"Users who made at least one purchase with amount > 100."

SELECT data->>'name'
FROM users
WHERE data @? '$.events[*] ? (@.type == "purchase" && @.amount > 100)';
-- Output: Ana, Carla

Equivalent with basic operators (already verbose):

SELECT DISTINCT u.data->>'name'
FROM users u, jsonb_array_elements(u.data->'events') e
WHERE e->>'type' = 'purchase'
  AND (e->>'amount')::numeric > 100;

JSON Path is 1 line, a natural read. Basic operators is 4 lines with an implicit lateral join and a manual cast. The difference shows.

Example 4: extracting values with jsonb_path_query

"For each user, return the amounts of their purchases."

SELECT
  data->>'name' AS user_name,
  jsonb_path_query(data, '$.events[*] ? (@.type == "purchase").amount') AS amount
FROM users;

-- Output:
--  user_name | amount
-- -----------+--------
--  Ana       | 150
--  Bob       | 50
--  Carla     | 300
--  Carla     | 200

jsonb_path_query returns one row per matching value, expanding arrays. This is what requires jsonb_array_elements with basic operators.

A useful variant — jsonb_path_query_array: it returns the matches in a single array per row (without expanding):

SELECT
  data->>'name',
  jsonb_path_query_array(data, '$.events[*] ? (@.type == "purchase").amount') AS amounts
FROM users;

-- Output:
--  ?column? | amounts
-- ----------+----------
--  Ana      | [150]
--  Bob      | [50]
--  Carla    | [300, 200]

Example 5: regex on strings

"Users whose name starts with 'A' or 'C'."

SELECT data->>'name'
FROM users
WHERE data @@ '$.name like_regex "^[AC]"';
-- Output: Ana, Carla

like_regex is one of the few ways to do pattern matching in JSONB without extracting and comparing in SQL.

Example 6: recursive descent (**)

"Users where at any level of the JSONB a purchase value appears."

SELECT data->>'name'
FROM users
WHERE data @? '$.** ? (@ == "purchase")';
-- Output: Ana, Bob, Carla (all of them, because they all have at least one purchase in events)

** is powerful but expensive — it visits every node of the JSONB. Useful for one-off exploration queries, not for hot queries.

Example 7: variables in path queries

JSON Path supports external variables passed as a second argument (a JSONB object):

SELECT data->>'name'
FROM users
WHERE jsonb_path_exists(
  data,
  '$.events[*] ? (@.type == "purchase" && @.amount > $min_amount)',
  '{"min_amount": 100}'::jsonb
);
-- Output: Ana, Carla

This is useful when the path is static but the values come from the app — the modern equivalent of parameterizing queries.

Indexability of JSON Path queries

This is important: not all path queries benefit from GIN equally.

Operational rule:

• For queries you're going to index and run very frequently, try to express them with @> first. GIN over @> is a well-proven and fast pattern.
• For queries that need logic that @> doesn't express (filters with >, like_regex, predicates over arrays of objects), use JSON Path. Accept that you'll probably need a partial index or an expression index to speed them up.

A typical case: the endpoint's main query uses @> (fast with GIN). The admin dashboard query that runs once per minute uses jsonb_path_query for flexibility — it's fine for it to be slower, it isn't a hot path.

Worked example: filtering complex transactions

Let's go to a realistic end-to-end case. We want to analyze a transactions table where each row has:

• metadata JSONB with {customer: {tier, country}, items: [{sku, qty, price}], discounts: [...]}

Question: "which transactions have at least one item with qty >= 5 AND price < 50, made by 'gold' tier customers in Spanish-speaking countries?"

Setup

DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
  id BIGSERIAL PRIMARY KEY,
  metadata JSONB NOT NULL
);

INSERT INTO transactions (metadata) VALUES
('{
  "customer": {"tier": "gold", "country": "MX"},
  "items": [
    {"sku": "ABC", "qty": 10, "price": 30},
    {"sku": "DEF", "qty": 1, "price": 200}
  ],
  "discounts": []
}'),
('{
  "customer": {"tier": "silver", "country": "AR"},
  "items": [
    {"sku": "XYZ", "qty": 5, "price": 40}
  ],
  "discounts": []
}'),
('{
  "customer": {"tier": "gold", "country": "ES"},
  "items": [
    {"sku": "PQR", "qty": 6, "price": 25},
    {"sku": "STU", "qty": 2, "price": 80}
  ],
  "discounts": [{"code": "SAVE10", "amount": 5}]
}'),
('{
  "customer": {"tier": "gold", "country": "US"},
  "items": [
    {"sku": "GHI", "qty": 8, "price": 20}
  ],
  "discounts": []
}');

The query with JSON Path

SELECT id
FROM transactions
WHERE metadata @? '$.customer ? (@.tier == "gold" && (@.country == "MX" || @.country == "ES" || @.country == "AR"))'
  AND metadata @? '$.items[*] ? (@.qty >= 5 && @.price < 50)';

-- Output: 1, 3

What it does:

1. First predicate: filters transactions where the customer is 'gold' tier and in a Spanish-speaking country.
2. Second predicate: filters the ones that have at least one item with a high qty and a low price.

The equivalent version with basic operators (to appreciate the contrast)

SELECT t.id
FROM transactions t
WHERE t.metadata @> '{"customer": {"tier": "gold"}}'
  AND t.metadata->'customer'->>'country' IN ('MX', 'ES', 'AR')
  AND EXISTS (
    SELECT 1 FROM jsonb_array_elements(t.metadata->'items') i
    WHERE (i->>'qty')::int >= 5
      AND (i->>'price')::numeric < 50
  );

It works, but it's longer and mixes three styles: @> for one part, ->/->> for another, an implicit lateral join for the items. JSON Path keeps a unified style.

EXPLAIN

EXPLAIN ANALYZE
SELECT id FROM transactions
WHERE metadata @? '$.customer ? (@.tier == "gold")';

-- Without an index:
-- Seq Scan on transactions
--   Filter: (metadata @? '$.customer ? (@.tier == "gold")'::jsonpath)

-- With CREATE INDEX ON transactions USING gin(metadata):
-- Bitmap Heap Scan on transactions
--   Recheck Cond: (metadata @? '$.customer ? (@.tier == "gold")'::jsonpath)
--   ->  Bitmap Index Scan on transactions_metadata_idx

Yes, GIN over metadata with jsonb_ops (the default) can speed up @? in many cases. This is documented and available since PostgreSQL 12+. The detail of exactly what works goes in capsule 05.

Why does this matter in real work?

1. Admin/analytics queries that with basic operators are horrible maintenance code.

Internal endpoints for the product team where there are complex filters over nested arrays. Without JSON Path, you end up with long CTEs full of jsonb_array_elements that nobody wants to modify. With JSON Path, one expressive line.

2. Validation of complex payloads.

If your API receives JSON payloads and you need to validate rules like "field X must exist AND there must be at least one item in Z that meets W," jsonb_path_exists gives it to you in one call. Useful for CHECK constraints and triggers.

ALTER TABLE orders ADD CONSTRAINT valid_items
  CHECK (data @? '$.items[*] ? (@.qty > 0 && @.price > 0)');

3. Ad-hoc data exploration.

In psql when you're debugging a dataset with JSONB, jsonb_path_query for "extract all the values that match this pattern at any level" is 10 times faster than writing a subquery with jsonb_array_elements.

4. It shows up in senior interviews.

"Do you know JSON Path queries in PostgreSQL?" — it separates the dev who learned JSONB in 2020 from the one who kept up with PostgreSQL 12+. Knowing how to use it (not necessarily memorizing it) demonstrates depth.

Traps and common mistakes

Mistake 1 (syntax): single vs double quotes

Symptom: ERROR: syntax error at or near "..." with JSON Path queries.

Why it happens: confusion between the SQL quotes (single) and the path ones (double).

-- ❌ Wrong: quotes inside and outside are both single
SELECT data @@ '$.country == 'US'' FROM events;

-- ✅ Right: SQL outside with single, path inside with double
SELECT data @@ '$.country == "US"' FROM events;

Mistake 2 (conceptual): confusing @@ with @?

Symptom: the query returns weird results because you chose the wrong operator.

Why it happens: both take a path expression, both return a boolean, but they expect different path expressions.

How to tell them apart: if your path ends in a comparison (> 100, == "x"), use @@ or @?. If your path is just an access ($.field) and you want to know whether it exists, use @?. When in doubt, use @? with a filter: data @? '$.field ? (@ == "x")'.

Mistake 3 (performance): using ** (recursive descent) in hot paths

Symptom: queries with $.** are slow on large tables.

Why it happens: ** visits every node of the JSONB. On large and deep documents, it's O(n) over the size of the document.

How to fix it: avoid ** in queries that run frequently. If you know the structure, specify the path. ** only for ad-hoc exploration.

Mistake 4 (conceptual): assuming jsonb_path_query filters rows

Symptom: SELECT id, jsonb_path_query(...) FROM my_table WHERE ... returns more rows than you expected.

Why it happens: jsonb_path_query is set-returning: for each input row, it returns N output rows (one per match inside the JSONB). If one row has 3 matches, you're going to see that row 3 times.

How to fix it:

• If you want all the expanded values: use jsonb_path_query and accept that the rows multiply.
• If you want one array per row: use jsonb_path_query_array.
• If you want only the first match: use jsonb_path_query_first.

-- 1 row per user, with an array of matches
SELECT data->>'name', jsonb_path_query_array(data, '$.events[*].type') FROM users;

-- 1 row per user, with the first match
SELECT data->>'name', jsonb_path_query_first(data, '$.events[*].type') FROM users;

-- N rows per user (one per match)
SELECT data->>'name', jsonb_path_query(data, '$.events[*].type') FROM users;

Mistake 5 (conceptual): expecting JSON Path to index everything automatically

Symptom: you created a GIN, wrote an "elegant" path query, and EXPLAIN shows a Seq Scan.

Why it happens: GIN speeds up @? with simple paths and @@ with basic predicates well, but it doesn't speed up queries with **, numeric comparisons (>, <), or like_regex well.

How to detect it: always EXPLAIN ANALYZE. If you see a Seq Scan, don't assume the path query is using the index.

How to fix it: for hot queries that don't use GIN, consider an expression index on the specific expression. For cold queries, accept the Seq Scan or narrow it with other indexable filters (WHERE created_at > now() - interval '1 day' AND data @@ ... — the indexable time filter cuts the dataset before evaluating the path).

Mistake 6 (syntax): forgetting the @ inside the filter

Symptom: ERROR: syntax error in JSON path when you write a filter.

Why it happens: inside a ? (...), you have to refer to the current element with @. Forgetting it is the most common mistake.

-- ❌ Wrong
data @? '$.tags[*] ? (== "admin")'

-- ✅ Right
data @? '$.tags[*] ? (@ == "admin")'

Exercises

Exercise 1: translate queries to JSON Path

The following queries use basic operators. Rewrite them with JSON Path and @? or @@:

a) WHERE data->>'country' = 'US' b) WHERE (data->>'age')::int >= 18 c) WHERE data->'tags' ? 'admin' d) WHERE EXISTS (SELECT 1 FROM jsonb_array_elements(data->'orders') o WHERE (o->>'total')::numeric > 500)

-- a)
WHERE data @@ '$.country == "US"'
-- or
WHERE data @? '$ ? (@.country == "US")'

-- b)
WHERE data @@ '$.age >= 18'

-- c)
WHERE data @? '$.tags[*] ? (@ == "admin")'

-- d)
WHERE data @? '$.orders[*] ? (@.total > 500)'

Exercise 2: extract nested values

Given the users table from the previous example, write a query that returns one row per purchase, showing the user's name and the amount of the purchase. Use jsonb_path_query.

SELECT
  data->>'name' AS user_name,
  jsonb_path_query(data, '$.events[*] ? (@.type == "purchase").amount') AS amount
FROM users;

-- Output:
--  user_name | amount
-- -----------+--------
--  Ana       | 150
--  Bob       | 50
--  Carla     | 300
--  Carla     | 200

SELECT
  data->>'name' AS user_name,
  SUM((amount #>> '{}')::numeric) AS total_purchased
FROM users,
LATERAL jsonb_path_query(data, '$.events[*] ? (@.type == "purchase").amount') AS amount
GROUP BY data->>'name';

Exercise 3: complex filters with AND/OR

Write a query that returns the users where:

• They have at least one event of type 'purchase' with amount > 100, OR
• They have at least one event where the timestamp starts at '2026-04-15' or later.

SELECT data->>'name'
FROM users
WHERE data @? '$.events[*] ? (@.type == "purchase" && @.amount > 100)'
   OR data @? '$.events[*] ? (@.ts >= "2026-04-15")';

WHERE data @? '$.events[*] ? ((@.type == "purchase" && @.amount > 100) || @.ts >= "2026-04-15")'

Exercise 4: regex on a field

Write a query that returns the users whose name matches the regex ^[AB] (starts with A or B).

SELECT data->>'name'
FROM users
WHERE data @@ '$.name like_regex "^[AB]"';
-- Output: Ana, Bob

SELECT data->>'name' FROM users WHERE data->>'name' ~ '^[AB]';

Exercise 5: validation with a CHECK constraint

Write an ALTER TABLE that adds a CHECK constraint to an orders table with a data JSONB column, validating that:

• data must have a customer_id key with a positive numeric value,
• data must have an items array with at least one element,
• each item in the array must have qty > 0 and price >= 0.

ALTER TABLE orders
ADD CONSTRAINT valid_order_data
CHECK (
  data @? '$.customer_id ? (@ > 0)'
  AND data @? '$.items[0]'
  AND NOT data @? '$.items[*] ? (@.qty <= 0 || @.price < 0)'
);

INSERT INTO orders (data) VALUES
('{"customer_id": 1, "items": [{"qty": 2, "price": 10}]}'); -- ✅ passes

INSERT INTO orders (data) VALUES
('{"customer_id": 1, "items": [{"qty": 0, "price": 10}]}'); -- ❌ qty=0
-- ERROR:  new row for relation "orders" violates check constraint "valid_order_data"

Exercise 6: when NOT to use JSON Path

For each case, decide whether you'd use JSON Path or basic operators. Justify it.

a) "List users whose country is 'US'" in an endpoint that's called 1000 times per second, with a 10M-row table.

b) "Return all the items where discount.percentage > 50 for a dashboard that loads once per minute."

c) "Validate that the JSONB sent by the user has the email key with a value that matches an email regex."

d) "List users that have at least one event where type = 'login' AND ts > '2026-01-01'."

Summary and next step

In this capsule you learned:

• JSON Path is a mini-DSL embedded in strings, activated by three operators: @@ (predicate true), @? (matches something), jsonb_path_query (returns values).
• Core syntax: $ is the root, .field is access, [*] is an array wildcard, ? (predicate) is a filter, @ is the current element.
• Predicate operators: ==, !=, <, >, &&, ||, !, like_regex, starts with.
• External variables with $name + a second JSONB argument.
• The big win of JSON Path is filters over arrays of objects with complex conditions. For simple queries over top-level keys, the basic operators are just as clear.
• Partial indexability: GIN helps with @? and some simple @@, but it doesn't speed up numeric comparisons or like_regex on its own. For hot paths, consider expression indexes or rewriting to @> when possible.
• Typical cases where JSON Path shines: validation with CHECK constraints, analytics/admin queries, complex filters on arrays.

Before moving on you should be able to:

• Distinguish when @? and @@ are appropriate, and when it's better to rewrite to @> to use GIN
• Write a filter over an array of objects without resorting to jsonb_array_elements
• Identify the @ inside the filters and not forget it
• Recognize that jsonb_path_query is set-returning and that it multiplies rows

Next capsule — GIN indexes on JSONB. All of capsules 03 and 04 talked about "this is sped up by GIN" and "this isn't." Now we go to the heart of the module: how GIN works internally, what happens when you create CREATE INDEX ... USING gin(...), and the module's most important decision: jsonb_ops (default, supports more operators) vs jsonb_path_ops (faster and more compact, @> only). You're going to remember this decision 6 months later in an interview.

Resources

1. PostgreSQL 16 Documentation — JSON Path Language — the complete reference for JSON Path syntax. Essential.
2. PostgreSQL 16 Documentation — jsonb_path_query and friends — all the path-related functions with examples.
3. PostgreSQL Wiki — JSON SQL/JSON Path — the history and motivation of the SQL/JSON Path standard.
4. pganalyze — Lukas Fittl — "JSON Path queries in PostgreSQL 12" — an applied analysis of the feature when it was introduced, with benchmarks and cases.
5. Crunchy Data — "Using PostgreSQL JSON Path Queries" — a practical tutorial with comparative examples.
6. Bruce Momjian — "PostgreSQL Improved JSON Support" — the core team's perspective on JSON Path.
7. SQL/JSON Path standard (ISO/IEC 9075-2:2016) — the official standard reference (only if you want to go deeper — not required).

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: GIN indexes on JSONB — the module's most important decision.