Operadores de ruta JSONB en PostgreSQL: #> y #>> para valores anidados

Cuando una columna guarda JSONB y el valor que necesitas esta enterrado tres o cuatro niveles abajo, una cadena de -> se convierte enseguida en una escalera ilegible. PostgreSQL ofrece dos operadores de ruta, #> y #>>, que recuperan un valor por ruta en una sola expresion. Veamos como funcionan y cuando conviene usar jsonb_path_query en su lugar.

Que hacen #> y #>>

Ambos operadores reciben una ruta como un array de texto text[] y la recorren de arriba abajo. La unica diferencia es el tipo del resultado:

• #> devuelve jsonb (un objeto anidado, un array o un escalar como JSON).
• #>> devuelve text (el escalar desempaquetado, sin comillas).

Supongamos que users tiene una columna profile jsonb con esta forma: {"address": {"city": "Berlin", "geo": [52.5, 13.4]}, "tags": ["pro", "eu"]}.

-- jsonb result: still a JSON object
SELECT profile #> '{address}' AS address_json
FROM users
WHERE id = 1;

-- text result: the raw city string, no quotes
SELECT profile #>> '{address,city}' AS city
FROM users
WHERE id = 1;

La regla mnemotecnica es simple: la > de mas exprime el valor hasta convertirlo en texto. Para WHERE, JOIN y comparaciones casi siempre quieres #>>, porque comparas contra una cadena normal.

Mas corto que la cadena de ->

La ventaja principal es que una sola ruta sustituye a varias flechas. Compara dos consultas equivalentes:

-- The arrow chain: noisy and easy to misread
SELECT profile -> 'address' ->> 'city' AS city
FROM users;

-- Same thing with a single path operator
SELECT profile #>> '{address,city}' AS city
FROM users;

Cuanto mas profunda es la estructura, mayor es la ganancia. La ruta '{a,b,c,d}' se lee como una unidad, mientras que -> 'a' -> 'b' -> 'c' ->> 'd' hay que descifrarla eslabon a eslabon.

Mezclar claves e indices de array

Una ruta puede alternar claves de objeto e indices de array en la misma cadena. Los indices son numeros escritos como texto, empiezan en cero y los negativos cuentan desde el final.

-- First geo coordinate (array index 0) inside the address object
SELECT profile #>> '{address,geo,0}' AS latitude
FROM users
WHERE id = 1;

-- Last tag using a negative index
SELECT profile #>> '{tags,-1}' AS last_tag
FROM users
WHERE id = 1;

Un ejemplo realista: orders tiene un meta jsonb con una lista de lineas, y quieres el SKU del primer articulo para los pedidos pagados.

-- meta = {"items": [{"sku": "A-1", "qty": 2}, ...]}
SELECT o.id,
       o.meta #>> '{items,0,sku}' AS first_sku
FROM orders o
WHERE o.status = 'paid';

Gotcha: si falta cualquier paso de la ruta, o accedes a una clave como si fuera indice (o al reves), el operador no lanza error; devuelve NULL en silencio. Es comodo, pero oculta erratas en el nombre de una clave: por el resultado no puedes distinguir "no hay datos" de "ruta incorrecta".

Cuando usar jsonb_path_query

Los operadores de ruta recuperan exactamente un valor en una ruta fija. En cuanto necesitas condiciones, recorrer todos los elementos de un array o comodines, pasa a jsonb_path_query y al lenguaje JSONPath (PostgreSQL 12+).

-- All SKUs across the whole items array, one row each
SELECT o.id, sku AS item_sku
FROM orders o,
     jsonb_path_query(o.meta, '$.items[*].sku') AS sku;

-- Path with a filter: items where qty > 1
SELECT o.id,
       jsonb_path_query(o.meta, '$.items[*] ? (@.qty > 1)') AS big_item
FROM orders o;

Aqui jsonb_path_query es una funcion que devuelve un conjunto de filas y va en el FROM, asi que su alias sku es la propia columna de valor: seleccionas el alias a secas, no sku.value. Cada coincidencia se vuelve su propia fila, a diferencia de los operadores escalares #> y #>>, que devuelven un unico valor dondequiera que los escribas.

Una guia rapida:

• Conoces la ruta exacta y quieres un valor: #> o #>>.
• Necesitas filtros, [*] o varias coincidencias: jsonb_path_query.
• Solo quieres un si/no en WHERE: el operador @? con JSONPath es mas corto e indexable con GIN.

Una ultima nota sobre rendimiento: tanto las expresiones #>> como JSONPath se pueden acelerar con un indice: uno de expresion (CREATE INDEX ON users ((profile #>> '{address,city}'))) para una ruta concreta, o un GIN con jsonb_path_ops para consultas flexibles.