Menu

JSONB en PostgreSQL: operadores, consultas e índices

Guarda y consulta JSON en PostgreSQL con jsonb: los operadores -> y ->>, la contención con @>, jsonb_set y otras actualizaciones, convertir arrays en filas, índices GIN y json vs jsonb.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

jsonb es el tipo de columna JSON binario de PostgreSQL. Lee campos con -> (devuelve jsonb) y ->> (devuelve text), encuentra filas con el operador de contención @> y cambia campos con jsonb_set o ||. Un índice GIN hace rápidas esas consultas.

Coinciden los pedidos 1 y 3. data->'customer' devuelve el objeto anidado como jsonb, ->>'name' saca el nombre como texto, y ->> más una conversión convierte el total en número.

Sacar valores de un JSONB

OperadorDevuelveEjemplo
-> clave o índicejsonbdata->'customer', data->'tags'->0
->> clave o índicetextdata->>'status'
#> rutajsonbdata#>'{customer,city}'
#>> rutatextdata#>>'{customer,city}'
[...] subíndice (PostgreSQL 14+)jsonbdata['customer']['city']

Los índices de los arrays empiezan en 0, y un índice negativo cuenta desde el final (-> -1 es el último elemento). Una clave que no existe da NULL de SQL, no un error, así que el pedido 3 de arriba tiene la ciudad en NULL.

Usa ->> para comparar con texto o para convertir: data->>'status' = 'paid'. Usa -> para seguir navegando. Un error común es data->'status' = 'paid'. PostgreSQL lee 'paid' como JSON, una palabra sin comillas no es JSON válido, y la consulta falla con "invalid input syntax for type json".

Buscar filas por su contenido JSON

@> comprueba si el valor de la izquierda contiene el de la derecha. Funciona a cualquier profundidad y dentro de arrays, y un índice GIN lo soporta:

Los productos 1 y 2 tienen talla M y precio; solo el 2 cuesta más de 30. Los operadores de búsqueda:

  • @> contiene, <@ está contenido en.
  • ? la clave (o la cadena del array) existe, ?| existe alguna de las claves, ?& existen todas.
  • @? y @@ ejecutan una consulta JSON path (PostgreSQL 12+), para comparaciones dentro del documento.

Actualizar campos dentro de un JSONB

PostgreSQL reescribe el valor completo en cada actualización, pero estas funciones te permiten describir solo el cambio:

  • jsonb_set(target, path, new_value) reemplaza un valor en una ruta.
  • - elimina una clave, y #- elimina una ruta anidada como '{notify,sms}'.
  • || añade o sobrescribe claves de primer nivel.

Desde PostgreSQL 14 también puedes asignar mediante un subíndice: SET prefs['notify']['email'] = 'false'.

Dos trampas:

  • jsonb_set devuelve NULL cuando el valor nuevo es NULL de SQL, y entonces el UPDATE borra la columna entera. Pasa COALESCE(to_jsonb(x), 'null'), o usa jsonb_set_lax (PostgreSQL 13+), que por defecto guarda un null de JSON.
  • jsonb_set añade una última clave que falta, pero no los padres que faltan: poner '{a,b}' sobre {} lo deja igual. La asignación por subíndice sí los crea.

Convertir arrays JSON en filas

jsonb_array_elements devuelve una fila por cada elemento del array, y jsonb_to_recordset convierte un array de objetos en columnas con tipo. Desde PostgreSQL 17, el estándar JSON_TABLE hace las dos cosas en un solo lugar:

A-1 vendió 3 y B-2 vendió 1. En sentido contrario, jsonb_build_object('id', id, 'name', name) construye un objeto a partir de columnas y jsonb_agg reúne filas en un array (ver string_agg y array_agg).

Indexar JSONB con GIN

Un índice GIN sobre toda la columna sirve para @>, ?, ?|, ?&, @? y @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

Escribe la consulta en la forma que soporta el índice. WHERE data @> '{"status": "paid"}' puede usar el índice GIN. WHERE data->>'status' = 'paid' no puede; necesita el índice de expresión. Una comparación de rango como (data->>'total')::numeric > 50 necesita un índice de expresión sobre esa expresión exacta.

JSON vs JSONB

jsonjsonb
Se guarda comoEl texto de entradaUna forma binaria ya analizada
Espacios, orden de claves, claves duplicadasSe conservanSe eliminan; gana el último duplicado
EscrituraAlgo más rápida (solo se valida)Se analiza al entrar
Lectura y consultasSe vuelve a analizar en cada accesoRápida
@>, ? e índices GINNoSí

json devuelve el texto sin cambios; jsonb se queda solo con "a": 3 y reordena las claves. Usa jsonb salvo que tengas que devolver el texto original byte a byte, como el payload guardado de un webhook. Los campos que tienen todas las filas, o que necesitan una clave foránea, van en columnas normales. La referencia oficial de funciones JSON enumera todos los operadores y funciones.

Preguntas frecuentes

¿Qué diferencia hay entre -> y ->> en PostgreSQL?

-> devuelve el valor como jsonb, así que puedes seguir navegando (data->'customer'->'city'). ->> lo devuelve como text, que es lo que comparas con una cadena o conviertes en número: (data->>'total')::numeric.

¿Cómo compruebo si un array JSONB contiene un valor?

Usa la contención: data->'tags' @> '["gift"]', o data @> '{"tags": ["gift"]}' para probar desde la raíz del documento. Para un array de cadenas, data->'tags' ? 'gift' también funciona. Un índice GIN sobre data acelera la segunda forma.

¿JSONB conserva el orden de las claves?

No. jsonb guarda las claves en su propio orden, elimina los espacios y se queda solo con la última de las claves duplicadas. Si necesitas recuperar exactamente el texto original, usa el tipo json.

¿Cómo compruebo si existe una clave en JSONB?

data ? 'key' es verdadero cuando el objeto de primer nivel tiene esa clave, aunque su valor sea null de JSON. ?| recibe un array y es verdadero si existe alguna de las claves, ?& si existen todas.

¿Puedo poner una clave foránea o NOT NULL a un campo dentro de JSONB?

No directamente. Una restricción CHECK puede comprobar un campo (CHECK (data ? 'email')), pero las claves foráneas necesitan una columna real. Los campos que tienen todas las filas y que otras tablas referencian van en columnas normales.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar