¿Cómo modifico campos dentro del nuevo tipo de datos JSON de PostgreSQL?
Con postgresql 9.3 puedo SELECTespecificar campos de un tipo de datos JSON, pero ¿cómo los modificas usando UPDATE? No puedo encontrar ningún ejemplo de esto en la documentación de PostgreSQL ni en ningún lugar en línea. He probado 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 jsonbfunciones de manipulación dentro del propio PostgreSQL (pero ninguna para json; se requieren conversiones para manipular jsonvalores).
Fusionar 2 (o más) objetos JSON (o concatenar 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, se puede configurar una clave simple usando:
SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')
Donde <key>debería estar la cadena y <value>puede ser cualquier tipo to_jsonb()que acepte.
Para establecer un valor en lo profundo de una jerarquía JSON , jsonb_set()se puede utilizar la función:
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)
pathTambién puede contener índices de matrices JSON y los enteros negativos que aparecen allí cuentan desde el final de las matrices JSON. Sin embargo, un índice de matriz JSON inexistente 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 una matriz JSON (conservando todos los valores originales) , jsonb_insert()se puede usar la función ( 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 números enteros negativos que aparecen pathcuentan desde el final de las matrices JSON.
Entonces, f.ej. agregar 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 (a jsonb_set()) cuando la pathentrada targetes 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
Se puede eliminar una clave (o un índice) de un objeto JSON (o de una matriz) 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]'
La eliminación, desde lo más profundo de una jerarquía JSON, se puede realizar 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 objeto json directamente con json_object_agg().
Respuesta original : También es posible (sin plpython o plv8) en SQL puro (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$;
Edición 2 : como señaló @ErwinBrandstetter , estas funciones anteriores funcionan como las llamadas UPSERT(actualiza un campo si existe, lo 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 claves solo pueden referirse a objetos internos, las matrices internas no son compatibles):
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$;
Actualizado: función agregada para reemplazar la clave de un campo json existente por otra clave determinada. Puede resultar útil para actualizar tipos de datos en migraciones u otros escenarios como la modificación de la estructura de datos.
CREATE OR REPLACE FUNCTION json_object_replace_key(
json_value json,
existing_key text,
desired_key text)
RETURNS json AS
$BODY$
SELECT COALESCE(
(
SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
FROM (
SELECT *
FROM json_each(json_value)
WHERE key <> existing_key
UNION ALL
SELECT desired_key, json_value -> existing_key
) AS "fields"
-- WHERE value IS NOT NULL (Actually not required as the string_agg with value's being null will "discard" that entry)
),
'{}'
)::json
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
Actualización : las funciones están compactadas ahora.
Con 9.5 use jsonb_set-
UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1;
donde cuerpo es un tipo de columna jsonb.
