tipo - PostgreSQL: Eliminar atributo de la columna JSON

postgresql jsonb example

Question

Necesito eliminar algunos atributos de una columna de tipo json.

La mesa:

CREATE TABLE my_table( id VARCHAR(80), data json);
INSERT INTO my_table (id, data) VALUES (
  ''A'', 
  ''{"attrA":1,"attrB":true,"attrC":["a", "b", "c"]}''
);

Ahora, necesito eliminar attrB de los data columna.

Algo así como alter table my_table drop column data->''attrB''; sería bueno. Pero un camino con una mesa temporal también sería suficiente.

Answer 1

Es un truco feo, pero si attrB no es su primera clave y aparece solo una vez, puede hacer lo siguiente:

UPDATE my_table SET data = REPLACE(data::text, '',"attrB":'' || (data->''attrB'')::text, '''')::json;

Answer 2

Esto se ha vuelto mucho más fácil con PostgreSQL 9.5 utilizando el tipo JSONB. Ver los operadores JSONB documentados here .

Puede eliminar un atributo de nivel superior con el operador "-".

SELECT ''{"a": {"key":"value"}, "b": 2, "c": true}''::jsonb - ''a''
// -> {"b": 2, "c": true}

Puede usar esto dentro de una llamada de actualización para actualizar un campo JSONB existente.

UPDATE my_table SET data = data - ''attrB''

También puede proporcionar el nombre del atributo dinámicamente a través de un parámetro si se utiliza en una función.

CREATE OR REPLACE FUNCTION delete_mytable_data_key(
    _id integer,
    _key character varying)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE my_table SET
        data = data - _key
    WHERE id = _id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

El operador inverso es el "||", para concatenar dos paquetes JSONB juntos. Tenga en cuenta que el uso más a la derecha del atributo sobrescribirá a los anteriores.

SELECT ''{"a": true, "c": true}''::jsonb || ''{"a": false, "b": 2}''::jsonb 
// -> {"a": false, "b": 2, "c": true}

Answer 3

No pude obtener SELECT ''{"a": "b"}''::jsonb - ''a''; Trabajar en 9.5.2. Sin embargo, SELECT ''{"a": "b"}''::jsonb #- ''{a}''; funcionó

Answer 4

Otra forma conveniente de hacer esto es usar la extensión hstore. De esta manera, puede escribir una función más conveniente para establecer / eliminar claves en un objeto json. Se me ocurrió la siguiente función para hacer lo mismo:

CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text)
 RETURNS json AS $$
 DECLARE item json;
 DECLARE fields hstore;
BEGIN
 -- Initialize the hstore with desired key being set to NULL
 fields := hstore(key_name,NULL);

 -- Parse through Input Json and push each key into hstore 
 FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
 LOOP
   --RAISE NOTICE ''Parsing Item % %'', item->>''key'', item->>''value'';
   fields := (fields::hstore || hstore(item->>''key'', item->>''value''));
 END LOOP;
 --RAISE NOTICE ''Result %'', hstore_to_json(fields);
 -- Remove the desired key from store
 fields := fields-key_name;

 RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

Un ejemplo simple de uso es:

SELECT remove_key((''{"Name":"My Name", "Items" :[{ "Id" : 1, "Name" : "Name 1"}, { "Id" : 2, "Name 2" : "Item2 Name"}]}'')::json, ''Name'');
-- Result
"{"Items": "[{ /"Id/" : 1, /"Name/" : /"Name 1/"}, { /"Id/" : 2, /"Name 2/" : /"Item2 Name/"}]"}"

Tengo otra función para hacer la operación set_key, así como lo siguiente:

CREATE OR REPLACE FUNCTION set_key(json_in json, key_name text, key_value text)
RETURNS json AS $$
DECLARE item json;
DECLARE fields hstore;
BEGIN
 -- Initialize the hstore with desired key value
 fields := hstore(key_name,key_value);

 -- Parse through Input Json and push each key into hstore 
 FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) AS r
 LOOP
   --RAISE NOTICE ''Parsing Item % %'', item->>''key'', item->>''value'';
   fields := (fields::hstore || hstore(item->>''key'', item->>''value''));
 END LOOP;
 --RAISE NOTICE ''Result %'', hstore_to_json(fields);
 RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

He discutido esto más en mi blog aquí.

Answer 5

Si bien esto es ciertamente más fácil en 9.5+ utilizando los operadores jsonb, la función que escribió pozs para eliminar múltiples claves sigue siendo útil. Por ejemplo, si las claves que se eliminarán se almacenan en una tabla, puede usar la función para eliminarlas todas. Aquí hay una función actualizada, que utiliza jsonb y postgresql 9.5+:

CREATE FUNCTION remove_multiple_keys(IN object jsonb, 
                                     variadic keys_to_delete text[], 
                                     OUT jsonb)
  IMMUTABLE
  STRICT
  LANGUAGE SQL
AS 
$$
  SELECT jsonb_object_agg(key, value)
     FROM (SELECT key, value 
           FROM jsonb_each("object")
           WHERE NOT (key = ANY("keys_to_delete")) 
     ) each_subselect
$$
;

Si las claves que se eliminarán se almacenan en una tabla (por ejemplo, en la columna "claves" de la tabla "table_with_keys"), podría llamar a esta función así:

SELECT remove_multiple_keys(my_json_object, 
                            VARIADIC (SELECT array_agg(keys) FROM table_with_keys));

Answer 6

Actualización : para 9.5+, hay operadores explícitos que puedes usar con jsonb (si tienes una columna escrita con json , puedes usar cast para aplicar una modificación):

La eliminación de una clave (o un índice) de un objeto JSON (o, 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]''

La eliminación, 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 un objeto json directamente con json_object_agg() .

Relacionados: otras manipulaciones JSON dentro de PostgreSQL:

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

Respuesta original (se aplica a PostgreSQL 9.3):

Si tiene al menos PostgreSQL 9.3, puede dividir su objeto en pares con json_each() y filtrar los campos no deseados, y luego construir el json nuevamente de forma manual. Algo como:

SELECT data::text::json AS before,
       (''{'' || array_to_string(array_agg(to_json(l.key) || '':'' || l.value), '','') || ''}'')::json AS after
FROM (VALUES (''{"attrA":1,"attrB":true,"attrC":["a","b","c"]}''::json)) AS v(data),
LATERAL (SELECT * FROM json_each(data) WHERE "key" <> ''attrB'') AS l
GROUP BY data::text

Con 9.2 (o inferior) no es posible.

Editar :

Una forma más conveniente es crear una función, que puede eliminar cualquier número de atributos en un campo json :

Edición 2 : string_agg() es menos costoso que array_to_string(array_agg())

CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT (''{'' || string_agg(to_json("key") || '':'' || "value", '','') || ''}'')
   FROM json_each("json")
   WHERE "key" <> ALL ("keys_to_delete")),
  ''{}''
)::json
$function$;

Con esta función, todo lo que necesita hacer es ejecutar la consulta a continuación:

UPDATE my_table
SET data = json_object_delete_keys(data, ''attrB'');