0Pricing
PostgreSQL Performance & Query Optimization · Lección

Consulta de JSONB con JSONPath

Aplique expresiones de ruta SQL/JSON para filtrar y extraer valores anidados con compatibilidad de índices.

Consulta de JSONB con JSONPath es una lección gratuita de PostgreSQL Performance & Query Optimization en CoddyKit. Esta es la lección 3 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de PostgreSQL Performance & Query Optimization, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en inglés.

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.

Preguntas frecuentes

¿La lección «Consulta de JSONB con JSONPath» es gratis?

Sí — el texto completo de «Consulta de JSONB con JSONPath» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de PostgreSQL Performance & Query Optimization, actualiza a CoddyKit PRO. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

¿Qué aprenderé en «Consulta de JSONB con JSONPath»?

Aplique expresiones de ruta SQL/JSON para filtrar y extraer valores anidados con compatibilidad de índices. Practicas PostgreSQL Performance & Query Optimization con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar PostgreSQL Performance & Query Optimization?

No se requiere experiencia previa. PostgreSQL Performance & Query Optimization en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 3 de 4.

¿Cuánto tiempo toma la lección «Consulta de JSONB con JSONPath»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de PostgreSQL Performance & Query Optimization?

Sí. Cada lección de PostgreSQL Performance & Query Optimization incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Operadores JSONB y consultas de contención
  2. GIN frente a índices de expresión en JSONB
  3. Consulta de JSONB con JSONPath
  4. Cuándo normalizar datos fuera de JSONB
← Volver a PostgreSQL Performance & Query Optimization