delete - ¿Cómo modifico los campos dentro del nuevo tipo de datos PostgreSQL JSON?
postgresql update json column (13)
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...
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 );
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.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.
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);
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'';
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.
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!
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'';
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"}}
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
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://.com/a/39076637
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(''<key>'', ''<value>'')
Donde <key> debería ser string, y <value> 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$;
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 noted 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.
UPDATE test
SET data = data::jsonb - ''a'' || ''{"a":5}''::jsonb
WHERE data->>''b'' = ''2''
Esto parece estar funcionando en PostgreSQL 9.5