0Pricing
PostgreSQL Performance & Query Optimization · レッスン

JSONPathによるJSONBのクエリ

SQL/JSONパス式を適用し、インデックスのサポートを受けながらネストした値をフィルタリングして抽出します。

「JSONPathによるJSONBのクエリ」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応の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.

よくある質問

「JSONPathによるJSONBのクエリ」レッスンは無料ですか？

はい。「JSONPathによるJSONBのクエリ」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「JSONPathによるJSONBのクエリ」で何を学びますか？

SQL/JSONパス式を適用し、インデックスのサポートを受けながらネストした値をフィルタリングして抽出します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応の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フィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. JSONB演算子と包含クエリ
  2. JSONBのGINインデックスと式インデックスの比較
  3. JSONPathによるJSONBのクエリ
  4. JSONBから正規化されたカラムへ移行するタイミング
← PostgreSQL Performance & Query Optimizationに戻る