Funciones JSON en PostgreSQL

Acceder a un campo en una columna JSONB

Supongamos que tenemos la tabla items, con la columna properties que es una columna JSONB con una fila que contiene estos datos:

{
    "datetime": "2023-05-02T11:28:29.341176",
    "name": "Item 32",
    "type": "Petrol Engine",
    "price": 1043.25
}

Si quisíéramos acceder al campo name del item con id 12345, la consulta sería:

SELECT id, properties->>'name' FROM item WHERE id = 12345;

Modificar valor de un campo en una columna JSONB

Si en la columna mencionada anteriormente quisiéramos ahora cambiar el campo price de la fila de dicho item 12345, la consulta sería:

UPDATE item 
    SET properties = jsonb_set(
        properties, '{price}', 
        to_jsonb(1043.25)
    ) 
WHERE id = 12345;

Comprobar la existencia de un determinado campo

Si queremos saber si en una columna JSONB existe un determinado campo, por ejemplo para saber cuántas entradas tienen dicho campo guardado en su JSONB, podemos usar el operador ?:

SELECT COUNT(*) FROM item WHERE properties ? 'category';

La consulta anterior nos devolvería el número de filas de la tabla item cuya columna properties (que es JSONB) tiene un campo category.