การค้นหา JSONB ด้วย JSONPath

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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".

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?

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.