Extraindo JSONB como texto: o operador ->> no PostgreSQL

No PostgreSQL uma coluna JSONB guarda dados estruturados dentro da propria linha, e na maioria das vezes voce so precisa de um valor escalar dela: um email de um perfil, um plano das configuracoes, um valor dos metadados de um pedido. O operador ->> faz exatamente isso: devolve um campo como text puro. Vamos ver como ele difere do ->, como converter o resultado em numero e por que ele retorna NULL em silencio.

->> versus ->

Os dois operadores buscam um valor por chave, mas retornam tipos diferentes. -> te da jsonb, enquanto ->> te da text. Essa e a diferenca central.

-- payload is a jsonb column on orders
-- -> keeps jsonb, ->> returns plain text
SELECT
  payload -> 'channel'  AS as_jsonb,   -- "web"  (with quotes, type jsonb)
  payload ->> 'channel' AS as_text     -- web    (no quotes, type text)
FROM orders;

Uma regra simples:

• Use -> quando voce esta descendo mais fundo e outro -> ou ->> vira em seguida.
• Use ->> no fim da cadeia, quando quer um valor escalar pronto para exibir ou comparar.

Para caminhos aninhados, combine-os: desca com -> e pegue a folha com ->>.

-- Nested: payload = {"shipping": {"city": "Berlin"}}
SELECT payload -> 'shipping' ->> 'city' AS city
FROM orders;

Convertendo o resultado para um tipo

->> sempre retorna text, mesmo quando o JSON guardava um numero ou um booleano. Para comparar numericamente ou somar, converta o resultado de forma explicita com ::.

-- (->> 'n')::int turns the text "3" into integer 3
SELECT
  id,
  (payload ->> 'items_count')::int   AS items,
  (payload ->> 'total')::numeric     AS total
FROM orders
WHERE (payload ->> 'total')::numeric > 100;

Gotcha: envolva ->> em parenteses antes de converter. Escrever payload ->> 'total'::numeric da erro, porque :: se liga ao literal de string 'total', nao ao valor extraido. A forma correta e (payload ->> 'total')::numeric.

NULL quando a chave nao existe

Se a chave nao esta no objeto, ->> nao lanca erro; ele retorna NULL. Isso e conveniente, mas arma uma cilada na filtragem.

-- Missing key gives NULL, not an error
SELECT payload ->> 'coupon' AS coupon
FROM orders;   -- NULL where there is no "coupon"

Por isso, ->> sozinho nao distingue "chave ausente" de "chave presente mas com valor JSON null" -- ambos resultam em NULL no SQL. Quando essa diferenca importa, teste a chave separadamente com o operador ?.

-- Distinguish a missing key from a present null
SELECT
  payload ? 'coupon'        AS has_key,     -- true / false
  payload ->> 'coupon'      AS coupon_text  -- text or NULL
FROM orders;

Filtrando por campos JSON

O caso mais comum e um WHERE sobre um campo dentro de JSONB. Como ->> retorna texto, compare-o contra uma string ou contra um tipo convertido.

-- String comparison: no cast needed
SELECT id, user_id
FROM orders
WHERE payload ->> 'channel' = 'mobile';

-- Numeric comparison: cast first
SELECT id
FROM orders
WHERE (payload ->> 'priority')::int >= 5;

Truques uteis ao filtrar:

• Converta tambem dentro dos agregados: SUM((payload ->> 'total')::numeric).
• Para pular linhas sem a chave, adicione payload ? 'channel' ou uma condicao IS NOT NULL.
• Para um filtro frequente por um campo, crie um indice de expressao: CREATE INDEX ON orders ((payload ->> 'channel')).

-- Average JSON total per channel, ignoring rows without the field
SELECT
  payload ->> 'channel'           AS channel,
  AVG((payload ->> 'total')::numeric) AS avg_total
FROM orders
WHERE payload ? 'total'
GROUP BY payload ->> 'channel';

MySQL e ClickHouse

Outros bancos usam outra sintaxe, embora a ideia seja a mesma.

• MySQL: ele tambem tem um operador ->> que retorna texto sem aspas; e acucar sobre JSON_UNQUOTE(JSON_EXTRACT(...)). O operador -> retorna o valor com aspas. O caminho e escrito como uma expressao do tipo '$.channel'.
• ClickHouse: nao ha operadores ->/->>; use funcoes como JSONExtractString(payload, 'channel') para texto e JSONExtractInt(...) para numeros; elas retornam o valor ja tipado.

-- MySQL: ->> unquotes to plain text
SELECT payload ->> '$.channel' AS channel
FROM orders;

-- ClickHouse: typed extractors instead of operators
SELECT JSONExtractString(payload, 'channel') AS channel
FROM orders;

Resumo: ->> e sua ferramenta principal quando voce precisa de um escalar do JSONB para exibir ou comparar; reserve -> para navegar na estrutura aninhada, sempre envolva ->> em parenteses antes de :: e lembre-se de que uma chave ausente resulta em NULL.