¿Cómo modifico los campos dentro del nuevo tipo de datos PostgreSQL JSON?

Con postgresql 9.3 puedo SELECCIONAR campos específicos de un tipo de datos JSON, pero ¿cómo los modifica usando UPDATE? No puedo encontrar ningún ejemplo de esto en la documentación postgresql, o en cualquier lugar en línea. He intentado lo obvio:

postgres=# create table test (data json); CREATE TABLE postgres=# insert into test (data) values ('{"a":1,"b":2}'); INSERT 0 1 postgres=# select data->'a' from test where data->>'b' = '2'; ?column? ---------- 1 (1 row) postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2'; ERROR: syntax error at or near "->" LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2... 

Actualización : con PostgreSQL 9.5 , hay algunas jsonb manipulación jsonb dentro de PostgreSQL en sí (pero no se requiere ninguna json para manipular los valores json ).

Fusionando 2 (o más) objetos JSON (o matrices concatenas):

 SELECT jsonb '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}' jsonb '["a",1]' || jsonb '["b",2]' -- will yield jsonb '["a",1,"b",2]' 

Entonces, establecer una clave simple se puede hacer usando:

 SELECT jsonb '{"a":1}' || jsonb_build_object('', '') 

Donde debería ser string, y puede ser cualquier tipo que to_jsonb() acepte.

Para establecer un valor profundo en una jerarquía JSON , se puede usar la función jsonb_set() :

 SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}') -- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}' 

Lista de parámetros completa de jsonb_set() :

 jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true) 

path puede contener índices de matriz JSON y los enteros negativos que aparecen allí cuentan desde el final de las matrices JSON. Sin embargo, un índice de matriz JSON no existente pero positivo agregará el elemento al final de la matriz:

 SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true) -- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}' 

Para insertar en la matriz JSON (conservando todos los valores originales) , se puede usar la función jsonb_insert() ( en 9.6+, esta función solamente, en esta sección ):

 SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2') -- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true) -- will yield jsonb '{"a":[null,{"b":[1,2]}]}' 

Lista de parámetros completa de jsonb_insert() :

 jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean default false) 

De nuevo, los enteros negativos que aparecen en la path cuentan desde el final de las matrices JSON.

Entonces, f.ex. Anexar al final de una matriz JSON se puede hacer con:

 SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true) -- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and 

Sin embargo, esta función funciona de forma ligeramente diferente (que jsonb_set() ) cuando la path en el target es la clave de un objeto JSON. En ese caso, solo agregará un nuevo par clave-valor para el objeto JSON cuando no se use la clave. Si se usa, generará un error:

 SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]') -- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]') -- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key 

La eliminación de una clave (o un índice) de un objeto JSON (o, a partir de una matriz) se puede hacer con el operador:

 SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}' jsonb '["a",1,"b",2]' - 1 -- will yield jsonb '["a","b",2]' 

Eliminar, desde lo más profundo de una jerarquía JSON se puede hacer con el operador #- :

 SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}' -- will yield jsonb '{"a":[null,{"b":[]}]}' 

Para 9.4 , puede usar una versión modificada de la respuesta original (a continuación), pero en lugar de agregar una cadena JSON, puede agregar en un objeto json directamente con json_object_agg() .

Respuesta original : es posible (sin plpython o plv8) en SQL puro también (pero necesita 9.3+, no funcionará con 9.2)

 CREATE OR REPLACE FUNCTION "json_object_set_key"( "json" json, "key_to_set" TEXT, "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_set" UNION ALL SELECT "key_to_set", to_json("value_to_set")) AS "fields" $function$; 

SQLFiddle

Editar :

Una versión que establece múltiples claves y valores:

 CREATE OR REPLACE FUNCTION "json_object_set_keys"( "json" json, "keys_to_set" TEXT[], "values_to_set" anyarray ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json FROM (SELECT * FROM json_each("json") WHERE "key" <> ALL ("keys_to_set") UNION ALL SELECT DISTINCT ON ("keys_to_set"["index"]) "keys_to_set"["index"], CASE WHEN "values_to_set"["index"] IS NULL THEN 'null'::json ELSE to_json("values_to_set"["index"]) END FROM generate_subscripts("keys_to_set", 1) AS "keys"("index") JOIN generate_subscripts("values_to_set", 1) AS "values"("index") USING ("index")) AS "fields" $function$; 

Editar 2 : como @ErwinBrandstetter notó que estas funciones anteriores funcionan como un llamado UPSERT (actualiza un campo si existe, inserta si no existe). Aquí hay una variante, que solo UPDATE :

 CREATE OR REPLACE FUNCTION "json_object_update_key"( "json" json, "key_to_set" TEXT, "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_to_set") IS NULL THEN "json" ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_set" UNION ALL SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json END $function$; 

Edición 3 : Aquí hay una variante recursiva, que puede establecer ( UPSERT ) un valor de hoja (y usa la primera función de esta respuesta), ubicado en una ruta clave (donde las teclas solo pueden referirse a objetos internos, arreglos internos no admitidos):

 CREATE OR REPLACE FUNCTION "json_object_set_path"( "json" json, "key_path" TEXT[], "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN to_json("value_to_set") WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set") ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_set_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json, "key_path"[l+1:u], "value_to_set" ) ) END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$; 

Actualización : las funciones se compactan ahora.

Con 9.5 use jsonb_set-

 UPDATE objects SET body = jsonb_set(body, '{name}', '"Mary"', true) WHERE id = 1; 

donde el cuerpo es un tipo de columna jsonb.

Con Postgresql 9.5 se puede hacer siguiendo-

 UPDATE test SET data = data - 'a' || '{"a":5}' WHERE data->>'b' = '2'; 

O

 UPDATE test SET data = jsonb_set(data, '{a}', '5'::jsonb); 

Alguien preguntó cómo actualizar muchos campos en el valor de jsonb a la vez. Supongamos que creamos una tabla:

 CREATE TABLE testjsonb ( id SERIAL PRIMARY KEY, object JSONB ); 

Luego, INSERTAR una fila experimental:

 INSERT INTO testjsonb VALUES (DEFAULT, '{"a":"one", "b":"two", "c":{"c1":"see1","c2":"see2","c3":"see3"}}'); 

Luego ACTUALIZAMOS la fila:

 UPDATE testjsonb SET object = object - 'b' || '{"a":1,"d":4}'; 

Que hace lo siguiente:

  1. Actualiza el campo a
  2. Elimina el campo b
  3. Agregue el campo d

Seleccionando los datos:

 SELECT jsonb_pretty(object) FROM testjsonb; 

Resultará en:

  jsonb_pretty ------------------------- { + "a": 1, + "c": { + "c1": "see1", + "c2": "see2", + "c3": "see3", + }, + "d": 4 + } (1 row) 

Para actualizar el campo dentro, no use el operador concat || . Use jsonb_set en su lugar. Lo cual no es simple:

 UPDATE testjsonb SET object = jsonb_set(jsonb_set(object, '{c,c1}','"seeme"'),'{c,c2}','"seehim"'); 

Usando el operador concat para {c, c1} por ejemplo:

 UPDATE testjsonb SET object = object || '{"c":{"c1":"seedoctor"}}'; 

Eliminará {c, c2} y {c, c3}.

Para obtener más potencia, busca potencia en la documentación de las funciones postgresql json . Uno podría estar interesado en el operador #- , la función jsonb_set y también la función jsonb_insert .

Para construir sobre las respuestas de @ pozs, aquí hay un par de funciones más de PostgreSQL que pueden ser útiles para algunos. (Requiere PostgreSQL 9.3+)

Eliminar por clave: borra un valor de la estructura JSON por clave.

 CREATE OR REPLACE FUNCTION "json_object_del_key"( "json" json, "key_to_del" TEXT ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_to_del") IS NULL THEN "json" ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_del" ) AS "fields")::json END $function$; 

Recursive Delete By Key: elimina un valor de la estructura JSON por key-path. (requiere la función json_object_set_key @ pozs)

 CREATE OR REPLACE FUNCTION "json_object_del_path"( "json" json, "key_path" TEXT[] ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json" ELSE CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN "json" WHEN 1 THEN "json_object_del_key"("json", "key_path"[l]) ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_del_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json, "key_path"[l+1:u] ) ) END END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$; 

Ejemplos de uso:

 s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}', 'foo'), json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}', '{"foo","moe"}'); json_object_del_key | json_object_del_path ---------------------+----------------------------------------- {"hello":[7,3,1]} | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}} 
 UPDATE test SET data = data::jsonb - 'a' || '{"a":5}'::jsonb WHERE data->>'b' = '2' 

Esto parece estar funcionando en PostgreSQL 9.5

Con PostgreSQL 9.4, implementamos la siguiente función python. También puede funcionar con PostgreSQL 9.3.

 create language plpython2u; create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$ import json a = json.loads(jdata) b = json.loads(jpaths) if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list': raise plpy.Error("The json data must be an object or a string.") if b.__class__.__name__ != 'list': raise plpy.Error("The json path must be an array of paths to traverse.") c = a for i in range(0, len(b)): p = b[i] plpy.notice('p == ' + str(p)) if i == len(b) - 1: c[p] = json.loads(jvalue) else: if p.__class__.__name__ == 'unicode': plpy.notice("Traversing '" + p + "'") if c.__class__.__name__ != 'dict': raise plpy.Error(" The value here is not a dictionary.") else: c = c[p] if p.__class__.__name__ == 'int': plpy.notice("Traversing " + str(p)) if c.__class__.__name__ != 'list': raise plpy.Error(" The value here is not a list.") else: c = c[p] if c is None: break return json.dumps(a) $$ language plpython2u ; 

Ejemplo de uso:

 create table jsonb_table (jsonb_column jsonb); insert into jsonb_table values ('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}'); select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table; update jsonb_table set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99'); select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table; 

Tenga en cuenta que para un empleador anterior, he escrito un conjunto de funciones C para manipular datos JSON como texto (no como un tipo json o jsonb ) para PostgreSQL 7, 8 y 9. Por ejemplo, extraer datos con json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']') , configuración de datos con json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87') y así sucesivamente. Le tomó cerca de 3 días de trabajo, por lo que si necesita ejecutar sistemas heredados y tener tiempo de sobra, puede valer la pena el esfuerzo. Me imagino que la versión C es mucho más rápida que la versión Python.

Aunque lo siguiente no satisfará esta solicitud (la función json_object_agg no está disponible en PostgreSQL 9.3), lo siguiente puede ser útil para cualquiera que busque un || operador para PostgreSQL 9.4, como se implementó en el próximo PostgreSQL 9.5:

 CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB) RETURNS JSONB AS $$ SELECT CASE WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN (SELECT json_object_agg(COALESCE(o.key, n.key), CASE WHEN n.key IS NOT NULL THEN n.value ELSE o.value END)::jsonb FROM jsonb_each($1) o FULL JOIN jsonb_each($2) n ON (n.key = o.key)) ELSE (CASE WHEN jsonb_typeof($1) = 'array' THEN LEFT($1::text, -1) ELSE '['||$1::text END ||', '|| CASE WHEN jsonb_typeof($2) = 'array' THEN RIGHT($2::text, -1) ELSE $2::text||']' END)::jsonb END $$ LANGUAGE sql IMMUTABLE STRICT; GRANT EXECUTE ON FUNCTION jsonb_merge(jsonb, jsonb) TO public; CREATE OPERATOR || ( LEFTARG = jsonb, RIGHTARG = jsonb, PROCEDURE = jsonb_merge ); 

Escribí una pequeña función para mí que funciona recursivamente en Postgres 9.4. 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 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, analiza en el fondo y actualiza / agrega valores donde sea necesario.

Lamentablemente, no he encontrado nada en la documentación, pero puede usar alguna solución, por ejemplo, podría escribir alguna función extendida.

Por ejemplo, en Python:

 CREATE or REPLACE FUNCTION json_update(data json, key text, value json) returns json as $$ from json import loads, dumps if key is None: return data js = loads(data) js[key] = value return dumps(js) $$ language plpython3u 

y entonces

 update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2'; 

El siguiente fragmento de plpython puede ser útil.

 CREATE EXTENSION IF NOT EXISTS plpythonu; CREATE LANGUAGE plpythonu; CREATE OR REPLACE FUNCTION json_update(data json, key text, value text) RETURNS json AS $$ import json json_data = json.loads(data) json_data[key] = value return json.dumps(json_data, indent=4) $$ LANGUAGE plpythonu; -- Check how JSON looks before updating SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1') FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis'; -- Once satisfied update JSON inplace UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1') WHERE record_id = 35 AND template = 'CFRDiagnosis'; 

Si su tipo de campo es de json, lo siguiente funcionará para usted.

 UPDATE table_name SET field_name = field_name::jsonb - 'key' || '{"key":new_val}' WHERE field_name->>'key' = 'old_value'. 

Operador ‘-‘ eliminar par de clave / valor o elemento de cadena del operando izquierdo. Los pares clave / valor se emparejan en función de su valor clave.

Operador ‘||’ Concatenar dos valores jsonb en un nuevo valor jsonb.

Dado que estos son operadores de jsonb, solo necesitas encasillar a :: jsonb

Más información: Funciones y operadores JSON

Puedes leer mi nota aquí

También puede incrementar las claves atómicamente dentro de jsonb esta manera:

 UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1; SELECT * FROM users; id | counters ----+------------ 1 | {"bar": 1} 

Tecla no definida -> asume el valor inicial de 0.

Para una explicación más detallada, vea mi respuesta aquí: https://stackoverflow.com/a/39076637

Esto funcionó para mí, al tratar de actualizar un campo de tipo de cadena.

 UPDATE table_name SET body = jsonb_set(body, '{some_key}', to_json('value'::TEXT)::jsonb); 

Espero que ayude a alguien más a salir!