Cómo realizar operaciones de actualización en columnas de tipo JSONB en Postgres 9.4

Si puede actualizar a Postgresql 9.5, el comando jsonb_set está disponible, como otros han mencionado.

En cada una de las siguientes sentencias SQL, he omitido la cláusula where por brevedad; obviamente, querrías agregar eso de nuevo.

Nombre de actualización:

UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

Reemplace las etiquetas (en oposición a agregar o eliminar etiquetas):

UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

Reemplazando la segunda etiqueta (0-indexada):

UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

Añadir una etiqueta ( esto funcionará siempre y cuando haya menos de 999 etiquetas; cambiar el argumento 999 a 1000 o superior genera un error . Este ya no parece ser el caso en Postgres 9.5.3; se puede usar un índice mucho más grande):

UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);

Elimina la última etiqueta:

UPDATE test SET data = data #- '{tags,-1}'

Actualización compleja (elimine la última etiqueta, inserte una nueva etiqueta y cambie el nombre):

UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), 
    '{name}', '"my-other-name"');

Es importante tener en cuenta que en cada uno de estos ejemplos, en realidad no está actualizando un solo campo de los datos JSON. En su lugar, está creando una versión temporal y modificada del datos, y la asignación de esa versión modificada de nuevo a la columna. En la práctica, el resultado debería ser el mismo, pero teniendo esto en cuenta debería hacer que las actualizaciones complejas, como el último ejemplo, sean más comprensibles.

En el ejemplo complejo, hay tres transformaciones y tres versiones temporales: Primero, se elimina la última etiqueta. Luego, esa versión se transforma agregando una nueva etiqueta. A continuación, la segunda versión se transforma cambiando el campo name. El valor en la columna data es sustituido por la versión final.

Esto viene en un 9,5 en la forma de jsonb_set por Andrew Dunstan basado en una extensión existente jsonbx que trabajar con el 9.4

Esta pregunta se hizo en el contexto de postgres 9.4, sin embargo, los nuevos espectadores que llegan a esta pregunta deben ser conscientes de que en postgres 9.5, las operaciones de Creación/Actualización/eliminación de sub-documentos en los campos JSONB son soportadas de forma nativa por la base de datos, sin la necesidad de funciones de extensión.

Ver: JSONB modificando operadores y funciones

Para aquellos que se encuentran con este problema y quieren una solución muy rápida (y están atascados en la versión 9.4.5 o anterior), esto es lo que hice:

Creación de la tabla de pruebas

CREATE TABLE test(id serial, data jsonb);
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Instrucción Update para cambiar el nombre de la propiedad jsonb

UPDATE test 
SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb 
WHERE id = 1;

En última instancia, la respuesta aceptada es correcta en el sentido de que no puede modificar una pieza individual de un objeto jsonb( en la versión 9.4.5 o anterior); sin embargo, puede convertir el objeto jsonb a una cadena (:: TEXT) y luego manipular la cadena y volver al objeto jsonb (:: jsonb).

Hay dos advertencias importantes

1. esto reemplazará todas las propiedades llamadas "name" en el json (en el caso de que tenga varias propiedades con el mismo nombre)
2. esto no es tan eficiente como lo sería jsonb_set si está utilizando 9.5

Dicho esto, me encontré con una situación en la que tuve que actualizar el esquema de contenido en los objetos jsonb y esta fue la forma más sencilla de lograr exactamente lo que el el póster original estaba preguntando.

Escribí una pequeña función para mí que funciona recursivamente en Postgres 9.4. Yo tenía el mismo problema (bueno que sí solucionaron parte de este dolor de cabeza en Postgres 9.5). De todos modos aquí está la función (espero que funcione bien para usted):

CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    v RECORD;
BEGIN
    IF jsonb_typeof(val2) = 'null'
    THEN 
        RETURN val1;
    END IF;

    result = val1;

    FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP

        IF jsonb_typeof(val2->v.key) = 'object'
            THEN
                result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
            ELSE
                result = result || jsonb_build_object(v.key, v.value);
        END IF;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Aquí está el uso de la muestra:

select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
                            jsonb_update                             
---------------------------------------------------------------------
 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)

Como puede ver, analice en profundidad y actualice/agregue valores cuando sea necesario.

Tal vez: UPDATE test SET data = '"my-other-name"':: json WHERE id = 1;

Funcionó con mi caso, donde los datos son de tipo json

Matheus de Oliveira creó funciones útiles para operaciones JSON CRUD en postgresql. Se pueden importar utilizando la directiva \i. Observe la bifurcación jsonb de las funciones si jsonb si su tipo de datos.

9.3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282