wake-up-neo.net

PostgreSQL: Attribut aus der JSON-Spalte entfernen

Ich muss einige Attribute aus einer Json-Typ-Spalte entfernen.

Der Tisch:

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"]}'
);

Nun muss ich attrB aus der Spalte data entfernen.

Etwas wie alter table my_table drop column data->'attrB'; wäre Nizza. Aber auch ein Weg mit einem temporären Tisch wäre ausreichend.

35
sja

Update: Für 9.5+ gibt es explizite Operatoren, die Sie mit jsonb verwenden können (wenn Sie über eine json-Spalte verfügen, können Sie mit Casts eine Modifikation anwenden):

Das Löschen eines Schlüssels (oder eines Index) aus einem JSON-Objekt (oder aus einem Array) kann mit dem Operator - durchgeführt werden:

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

Das Löschen aus einer JSON-Hierarchie kann mit dem Operator #- durchgeführt werden:

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

Für 9.4 können Sie eine modifizierte Version der ursprünglichen Antwort (unten) verwenden. Anstatt jedoch eine JSON-Zeichenfolge zu aggregieren, können Sie sie mit json_object_agg() direkt in ein json-Objekt aggregieren.

Verwandte: andere JSON-Manipulationen in PostgreSQL:

Originalantwort (gilt für PostgreSQL 9.3):

Wenn Sie mindestens über PostgreSQL 9.3 verfügen, können Sie Ihr Objekt mit json_each() in Paare unterteilen, Ihre unerwünschten Felder filtern und den Json dann manuell erneut aufbauen. So etwas wie:

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

Mit 9.2 (oder niedriger) ist dies nicht möglich.

Bearbeiten:

Eine bequemere Form ist das Erstellen einer Funktion, mit der eine beliebige Anzahl von Attributen in einem json-Feld entfernt werden kann:

Edit 2: string_agg() ist weniger teuer als 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$;

Mit dieser Funktion müssen Sie nur die folgende Abfrage ausführen:

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

Dies ist mit PostgreSQL 9.5 mit dem JSONB-Typ wesentlich einfacher geworden. Siehe JSONB-Operatoren dokumentiert hier .

Sie können ein Attribut der obersten Ebene mit dem Operator "-" entfernen.

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

Sie können dies innerhalb eines Aktualisierungsaufrufs verwenden, um ein vorhandenes JSONB-Feld zu aktualisieren. 

UPDATE my_table SET data = data - 'attrB'

Sie können den Attributnamen auch dynamisch über Parameter angeben, wenn er in einer Funktion verwendet wird.

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;

Der umgekehrte Operator ist das "||", um zwei JSONB-Pakete miteinander zu verketten. Beachten Sie, dass die Verwendung des Attributs ganz rechts alle vorherigen überschreibt.

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

Es ist ein hässlicher Hack, aber wenn attrB nicht Ihr erster Schlüssel ist und nur einmal angezeigt wird, können Sie Folgendes tun:

UPDATE my_table SET data = REPLACE(data::text, ',"attrB":' || (data->'attrB')::text, '')::json;
3
KARASZI István

Ich konnte SELECT '{"a": "b"}'::jsonb - 'a'; nicht in 9.5.2 zum Laufen bringen. SELECT '{"a": "b"}'::jsonb #- '{a}'; hat jedoch funktioniert!

2
Westy92

Eine andere bequeme Möglichkeit besteht darin, die Erweiterung von hstore zu verwenden. Auf diese Weise können Sie eine bequemere Funktion zum Festlegen/Löschen von Tasten in einem Json-Objekt schreiben. Ich hatte folgende Funktion, um dasselbe zu tun:

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;

Ein einfaches Anwendungsbeispiel ist:

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\"}]"}"

Ich habe noch eine weitere Funktion, um die set_key -Operation auszuführen:

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;

Ich habe dies mehr in meinem blog hier diskutiert.

1
Sumit Chawla

Während dies mit 9.5 und jsonb-Operatoren sicherlich einfacher ist, ist die Funktion, die geschrieben wurde, um mehrere Schlüssel zu entfernen, immer noch nützlich. Wenn die zu entfernenden Schlüssel beispielsweise in einer Tabelle gespeichert sind, können Sie die Funktion verwenden, um sie alle zu entfernen. Hier ist eine aktualisierte Funktion, die jsonb und postgresql 9.5+ verwendet:

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
$$
;

Wenn die zu entfernenden Schlüssel in einer Tabelle gespeichert sind (z. B. in der Spalte "Schlüssel" der Tabelle "table_with_keys"), können Sie diese Funktion folgendermaßen aufrufen:

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