PostgreSQL Performance & Query Optimization · Lesson

Querying JSONB with JSONPath

Apply SQL/JSON path expressions to filter and extract nested values with index support.

Querying JSONB with JSONPath is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why JSONPath for JSONB

PostgreSQL stores semi-structured data in the jsonb type. Pulling nested values with the classic -> and ->> operators works, but gets clumsy fast for deep paths, arrays, and conditional filters.

The SQL/JSON path language (added in PostgreSQL 12) gives you a compact, expressive way to navigate and filter JSON. It powers two key functions:

jsonb_path_query / jsonb_path_query_array — extract matching values
jsonb_path_exists and the @? / @@ operators — test predicates

Best of all, those operators can be accelerated by a GIN index, which is exactly what this lesson is about.

A sample JSONB document

Imagine an orders table with a data jsonb column. A single row might hold a document like the one below.

Throughout this lesson we will navigate into customer, iterate over the items array, and filter by numeric and string conditions.

SELECT '{
  "id": 1042,
  "status": "shipped",
  "customer": { "name": "Mara", "tier": "gold" },
  "items": [
    { "sku": "A-1", "qty": 2, "price": 19.90 },
    { "sku": "B-7", "qty": 1, "price": 4.50 }
  ]
}'::jsonb AS data;

The $ root and dot navigation

Every JSONPath expression starts at $, the context item (the whole document). From there you use dot notation for object keys.

$.status → the value of the status key
$.customer.name → a nested value

jsonb_path_query returns each match as a jsonb value. Note that string results keep their quotes; use jsonb_path_query_first(...) #>> '{}' or a cast if you need plain text.

SELECT jsonb_path_query(
  '{"status":"shipped","customer":{"name":"Mara"}}'::jsonb,
  '$.customer.name'
) AS name;

Walking into arrays

To reach array elements, use square brackets. Indexes are zero-based.

$.items[0] → the first element
$.items[*] → the wildcard, every element
$.items[*].sku → the sku of every element

When a path matches many values, jsonb_path_query returns one row per match. Wrap the call in jsonb_path_query_array to collect them into a single JSON array instead.

SELECT jsonb_path_query_array(
  '{"items":[{"sku":"A-1"},{"sku":"B-7"}]}'::jsonb,
  '$.items[*].sku'
) AS skus;

Filter expressions with ? ( )

The real power of JSONPath is the filter expression: ? ( predicate ). Inside a filter, @ refers to the current item being tested.

To get every item whose quantity is at least 2:

$.items[*] ? (@.qty >= 2)

The filter keeps only the array elements that satisfy the predicate. You can then keep navigating, e.g. $.items[*] ? (@.qty >= 2).sku to return just their SKUs.

SELECT jsonb_path_query(
  '{"items":[{"sku":"A-1","qty":2},{"sku":"B-7","qty":1}]}'::jsonb,
  '$.items[*] ? (@.qty >= 2).sku'
) AS heavy_skus;

Combining predicates and operators

Filter predicates support the usual comparison operators (==, !=, <, <=, >, >=) and the boolean connectives && and ||.

Note the equality operator inside JSONPath is ==, not the SQL single =. Strings are written with double quotes.

$.items[*] ? (@.qty > 1 && @.price < 10)
$ ? (@.customer.tier == "gold")

Parentheses let you group complex logic just like in SQL.

SELECT jsonb_path_query(
  '{"items":[{"sku":"A-1","qty":2,"price":19.9},{"sku":"C-9","qty":3,"price":4.5}]}'::jsonb,
  '$.items[*] ? (@.qty > 1 && @.price < 10)'
) AS cheap_bulk;

Testing existence: @? and @@

For filtering rows in a WHERE clause you usually want a boolean, not the matched value. Two operators do this:

jsonb @? jsonpath → true if the path returns any item
jsonb @@ jsonpath → evaluates a path that itself yields a boolean predicate

Rule of thumb: with @? the filter lives inside the path ($.items[*] ? (@.qty > 5)); with @@ the path is the predicate ($.customer.tier == "gold"). Both are GIN-indexable.

SELECT
  data @? '$.items[*] ? (@.qty > 5)'        AS has_bulk_item,
  data @@ '$.customer.tier == "gold"'        AS is_gold
FROM (SELECT '{"customer":{"tier":"gold"},"items":[{"qty":2}]}'::jsonb AS data) t;

Filtering rows in a real query

Here is the pattern you will write most often: select rows whose JSONB document satisfies a path predicate. Because @? is indexable, this can run without a sequential scan once the right index exists.

This query finds shipped orders that contain at least one item costing more than 100.

SELECT id, data->>'status' AS status
FROM orders
WHERE data @? '$.items[*] ? (@.price > 100)'
  AND data @@ '$.status == "shipped"';

Indexing with the default jsonb_ops GIN

A plain GIN index on the column uses the jsonb_ops operator class. It indexes every key and every value, supporting containment (@>), key-existence (?), and the JSONPath operators @? / @@.

It is flexible but larger, because each value gets its own index entry.

CREATE INDEX idx_orders_data ON orders USING gin (data);

-- Now this predicate can use the index:
EXPLAIN ANALYZE
SELECT id FROM orders
WHERE data @? '$.items[*] ? (@.price > 100)';

Smaller and faster: jsonb_path_ops

If you only need containment and JSONPath search (not the standalone key-existence ? operator), the jsonb_path_ops operator class is the better choice.

It hashes whole key+value paths into single index entries, so the index is smaller and usually faster for @>, @?, and @@ lookups. The trade-off: it does not support the bare ?, ?|, ?& key-existence operators.

CREATE INDEX idx_orders_data_path
  ON orders USING gin (data jsonb_path_ops);

-- Great for: data @? '$.items[*] ? (@.price > 100)'
-- Not for:   data ? 'status'

Expression indexes for hot scalar paths

GIN is ideal for flexible containment search. But if you constantly filter on one scalar value — say status — a targeted B-tree expression index on the extracted text is smaller and supports ordering and range scans.

Extract once with (data->>'status') and index that expression.
The query WHERE clause must use the same expression for the planner to use it.

Use GIN for "does the document contain X?" and B-tree expression indexes for "equals / ordered-by this one field".

CREATE INDEX idx_orders_status
  ON orders ((data->>'status'));

SELECT id FROM orders
WHERE data->>'status' = 'shipped'
ORDER BY (data->>'status');

Quick Check

You need a GIN index that accelerates JSONPath predicates like data @? '$.items[*] ? (@.price > 100)' and you want the smallest, fastest index. You do not need the bare key-existence operator ?. Which index should you create?

Recap

You learned how to query JSONB with the SQL/JSON path language and how to make it fast:

Navigate from $ using dots for keys and [*] for arrays.
Filter with ? (@ ... ), using == for equality and && / || to combine predicates.
Extract matches with jsonb_path_query / jsonb_path_query_array.
Test in WHERE clauses with @? (filter inside the path) and @@ (path is the predicate).
Index with GIN: default jsonb_ops for full flexibility, or jsonb_path_ops for smaller, faster containment and path search; reach for a B-tree expression index when you repeatedly filter or sort by a single scalar field.

Pick the index that matches your access pattern, and always confirm with EXPLAIN ANALYZE.

Frequently asked questions

Is the “Querying JSONB with JSONPath” lesson free?

Yes — the full text of “Querying JSONB with JSONPath” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Querying JSONB with JSONPath”?

Apply SQL/JSON path expressions to filter and extract nested values with index support. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Querying JSONB with JSONPath” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization