Extraer JSONB como texto: el operador ->> en PostgreSQL

En PostgreSQL una columna JSONB guarda datos estructurados dentro de la propia fila, y casi siempre solo necesitas un valor escalar de ella: un email de un perfil, un plan de la configuracion, un importe de los metadatos de un pedido. El operador ->> hace justo eso: devuelve un campo como text plano. Veamos en que se diferencia de ->, como convertir el resultado a numero y por que devuelve NULL en silencio.

->> frente a ->

Ambos operadores obtienen un valor por clave, pero devuelven tipos distintos. -> te da jsonb, mientras que ->> te da text. Esa es la diferencia clave.

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

Una regla sencilla:

• Usa -> cuando bajas en profundidad y le seguira otro -> o ->>.
• Usa ->> al final de la cadena, cuando quieres un valor escalar listo para mostrar o comparar.

Para rutas anidadas, combinalos: baja con -> y toma la hoja con ->>.

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

Convertir el resultado a un tipo

->> siempre devuelve text, incluso si el JSON contenia un numero o un booleano. Para comparar numericamente o sumar, convierte el resultado de forma explicita con ::.

-- (->> '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: envuelve ->> entre parentesis antes de convertir. Escribir payload ->> 'total'::numeric da error, porque :: se enlaza al literal de cadena 'total', no al valor extraido. La forma correcta es (payload ->> 'total')::numeric.

NULL cuando falta la clave

Si la clave no esta en el objeto, ->> no lanza un error; devuelve NULL. Es comodo, pero tiende una trampa al filtrar.

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

Por eso, ->> por si solo no distingue "clave ausente" de "clave presente pero con valor JSON null"; ambos dan NULL en SQL. Cuando esa diferencia importa, comprueba la clave por separado con el 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;

Filtrar por campos JSON

El caso mas comun es un WHERE sobre un campo dentro de JSONB. Como ->> da texto, comparalo contra una cadena o contra un 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;

Trucos utiles al filtrar:

• Convierte tambien dentro de los agregados: SUM((payload ->> 'total')::numeric).
• Para saltar filas sin la clave, anade payload ? 'channel' o una condicion IS NOT NULL.
• Para un filtro frecuente por un campo, crea un indice de expresion: 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 y ClickHouse

Otros motores usan otra sintaxis, aunque la idea es la misma.

• MySQL: tambien tiene un operador ->> que devuelve texto sin comillas; es azucar sobre JSON_UNQUOTE(JSON_EXTRACT(...)). El operador -> devuelve el valor con comillas. La ruta se escribe como una expresion del tipo '$.channel'.
• ClickHouse: no hay operadores ->/->>; usa funciones como JSONExtractString(payload, 'channel') para texto y JSONExtractInt(...) para numeros; devuelven el valor ya 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;

En resumen: ->> es tu herramienta principal cuando necesitas un escalar de JSONB para mostrar o comparar; reserva -> para navegar por la estructura anidada, envuelve siempre ->> entre parentesis antes de :: y recuerda que una clave ausente devuelve NULL.