PostgreSQL Performance & Query Optimization · 课时

使用 JSONPath 查询 JSONB

应用 SQL/JSON 路径表达式，在索引支持下筛选并提取嵌套值。

第 3 / 4 课13 个步骤

使用 JSONPath 查询 JSONB 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 22
课程: 88

免费开始学习查看完整课程

常见问题解答

「使用 JSONPath 查询 JSONB」课时是免费的吗？

是的 — 「使用 JSONPath 查询 JSONB」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「使用 JSONPath 查询 JSONB」这节课中我会学到什么？

应用 SQL/JSON 路径表达式，在索引支持下筛选并提取嵌套值。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「使用 JSONPath 查询 JSONB」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization