¿Cómo puedo modificar los campos dentro del nuevo tipo de datos JSON de PostgreSQL?
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 de 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...
13 answers
Actualizar: Con PostgreSQL 9.5 , hay algunas funciones de manipulación jsonb
dentro de PostgreSQL (pero ninguna para json
; se requieren casts para manipular valores json
).
Fusionando 2 (o más) objetos JSON (o concatenando matrices):
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 tecla simple se puede hacer usando:
SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')
Donde <key>
debe ser cadena, 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 completa de parámetros de jsonb_set()
:
jsonb_set(target jsonb,
path text[],
new_value jsonb,
create_missing boolean default true)
path
puede contener índices de matriz JSON también y enteros negativos que aparecen cuentan desde el final de las matrices JSON. Sin embargo, un índice de matriz JSON no existente, pero positivo, anexará 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 solo, 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 completa de parámetros de jsonb_insert()
:
jsonb_insert(target jsonb,
path text[],
new_value jsonb,
insert_after boolean default false)
Nuevamente, los enteros negativos que aparecen en path
cuentan desde el final de los arrays JSON.
Así que, f.ex. añadir al final de un array 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 está trabajando ligeramente diferente (que jsonb_set()
) cuando el path
en target
es la clave de un objeto JSON. En ese caso, solo agregará un nuevo par clave-valor para el JSON objeto cuando la clave no se utiliza. 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, de un array) 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 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 agregarla en un json objeto 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$;
Edit :
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 señaló 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$;
Edit 3: Aquí está la variante recursiva, que puede establecer (UPSERT
) un valor de hoja (y utiliza la primera función de esta respuesta), ubicada en una ruta de claves (donde las claves solo pueden referirse a objetos internos, matrices internas no soportadas):
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$;
Update : las funciones están compactadas ahora.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-30 11:20:19
Con 9.5 use jsonb_set -
UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1;
Donde body es un tipo de columna jsonb.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-26 15:43:11
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 jsonb a la vez. Supongamos que creamos una tabla:
CREATE TABLE testjsonb ( id SERIAL PRIMARY KEY, object JSONB );
Luego INSERTAMOS 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:
- Actualiza el campo a
- Elimina el campo b
- Añadir el campo d
Seleccionando el 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 utilice el operador concat ||
. Utilice jsonb_set en su lugar. Que 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 más potencia, busque potencia en documentación de funciones json de postgresql. Uno podría estar interesado en el operador #-
, la función jsonb_set
y también la función jsonb_insert
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-18 07:09:22
Para construir sobre las respuestas de @pozs, aquí hay un par más de funciones PostgreSQL que pueden ser útiles para algunos. (Requiere PostgreSQL 9.3+)
Eliminar por clave: Elimina 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$;
Delete Recursivo Por Clave: Elimina un valor de la estructura JSON por ruta de clave. (requiere la función json_object_set_key
de @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"}}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-14 20:05:23
UPDATE test
SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
WHERE data->>'b' = '2'
Esto parece estar funcionando en PostgreSQL 9.5
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-07-07 11:47:49
Con PostgreSQL 9.4, hemos implementado la siguiente función de 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, extrayendo datos con json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']')
, configurando datos con json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87')
y así sucesivamente. Tomó alrededor de 3 días de trabajo, por lo que si necesita que se ejecute en sistemas heredados y tener el tiempo para de repuesto, puede valer la pena el esfuerzo. Imagino que la versión C es mucho más rápida que la versión python.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-02-24 00:36:23
Aunque lo siguiente no satisfaga 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 );
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-03-08 13:29:34
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 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-10-13 19:48:21
Lamentablemente, no he encontrado nada en la documentación, pero puede usar alguna solución alternativa, 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 luego
update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-24 12:37:38
El siguiente fragmento de plpython podría 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';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-07-29 10:46:44
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'.
Operator '-' delete key/value pair or string element from left operand. Los pares clave / valor se emparejan en función de su valor clave.
El operador '||' concatena dos valores jsonb en un nuevo valor jsonb.
Dado que estos son operadores jsonb, solo necesita encasillar a:: jsonb
Más información: Funciones y operadores JSON
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-03 13:02:12
También puedes incrementar las claves atómicamente dentro de jsonb
así:
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}
Undefined key -> asume el valor inicial de 0.
Para una explicación más detallada, vea mi respuesta aquí: https://stackoverflow.com/a/39076637
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:20
Esto funcionó para mí, al intentar 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!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-09 12:57:52