Operadores de caminho JSONB no PostgreSQL: #> e #>> para valores aninhados

Quando uma coluna guarda JSONB e o valor de que voce precisa esta enterrado tres ou quatro niveis abaixo, uma cadeia de -> vira rapidamente uma escada ilegivel. O PostgreSQL oferece dois operadores de caminho, #> e #>>, que buscam um valor por caminho em uma unica expressao. Vamos ver como eles funcionam e quando vale a pena usar jsonb_path_query.

O que #> e #>> fazem

Os dois operadores recebem um caminho como um array de texto text[] e o percorrem de cima para baixo. A unica diferenca e o tipo do resultado:

• #> retorna jsonb (um objeto aninhado, um array ou um escalar como JSON).
• #>> retorna text (o escalar desempacotado, sem aspas).

Suponha que users tem uma coluna profile jsonb com este formato: {"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;

O macete e simples: o > extra espreme o valor ate virar texto. Para WHERE, JOIN e comparacoes voce quase sempre quer #>>, porque esta comparando com uma string comum.

Mais curto que a cadeia de ->

A vantagem principal e que um unico caminho substitui varias setas. Compare duas 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;

Quanto mais profunda a estrutura, maior o ganho. O caminho '{a,b,c,d}' se le como uma unidade, enquanto -> 'a' -> 'b' -> 'c' ->> 'd' precisa ser decifrado elo por elo.

Misturar chaves e indices de array

Um caminho pode alternar chaves de objeto e indices de array na mesma string. Os indices sao numeros escritos como texto, comecam em zero e os negativos contam a partir do fim.

-- 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;

Um exemplo realista: orders tem um meta jsonb com uma lista de itens, e voce quer o SKU do primeiro item para os pedidos pagos.

-- 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: se qualquer passo do caminho estiver ausente, ou voce acessar uma chave como se fosse indice (ou o contrario), o operador nao lanca erro; ele retorna NULL em silencio. E pratico, mas mascara erros de digitacao no nome de uma chave: pelo resultado voce nao distingue "sem dados" de "caminho errado".

Quando usar jsonb_path_query

Os operadores de caminho buscam exatamente um valor em um caminho fixo. No momento em que voce precisa de condicoes, percorrer todos os elementos de um array ou curingas, mude para jsonb_path_query e a linguagem 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 e uma funcao que devolve um conjunto de linhas e fica no FROM, entao seu alias sku e a propria coluna de valor: voce seleciona o alias puro, nao sku.value. Cada correspondencia vira sua propria linha, ao contrario dos operadores escalares #> e #>>, que devolvem um unico valor onde quer que voce os escreva.

Um guia rapido:

• Voce sabe o caminho exato e quer um valor: #> ou #>>.
• Voce precisa de filtros, [*] ou varias correspondencias: jsonb_path_query.
• Voce so quer um sim/nao no WHERE: o operador @? com JSONPath e mais curto e indexavel com GIN.

Uma ultima palavra sobre desempenho: tanto as expressoes #>> quanto o JSONPath podem ser acelerados com um indice: um de expressao (CREATE INDEX ON users ((profile #>> '{address,city}'))) para um caminho especifico, ou um GIN com jsonb_path_ops para consultas flexiveis.