0Pricing
PostgreSQL Performance & Query Optimization · Lezione

Interrogazione di JSONB con JSONPath

Applichi espressioni di percorso SQL/JSON per filtrare ed estrarre valori annidati con il supporto degli indici.

Interrogazione di JSONB con JSONPath è una lezione PostgreSQL Performance & Query Optimization gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento PostgreSQL Performance & Query Optimization, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Parti di questa lezione non sono ancora state tradotte e vengono mostrate in inglese.

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.

Domande Frequenti

La lezione «Interrogazione di JSONB con JSONPath» è gratuita?

Sì — il testo completo di «Interrogazione di JSONB con JSONPath» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso PostgreSQL Performance & Query Optimization, passa a CoddyKit PRO. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Cosa imparerò in «Interrogazione di JSONB con JSONPath»?

Applichi espressioni di percorso SQL/JSON per filtrare ed estrarre valori annidati con il supporto degli indici. Eserciti PostgreSQL Performance & Query Optimization con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare PostgreSQL Performance & Query Optimization?

Non è richiesta alcuna esperienza precedente. PostgreSQL Performance & Query Optimization su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Interrogazione di JSONB con JSONPath»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione PostgreSQL Performance & Query Optimization?

Sì. Ogni lezione PostgreSQL Performance & Query Optimization include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Operatori JSONB e query di contenimento
  2. Indici GIN e indici su espressioni per JSONB
  3. Interrogazione di JSONB con JSONPath
  4. Quando normalizzare i dati fuori da JSONB
← Torna a PostgreSQL Performance & Query Optimization