Wie ändere ich Felder innerhalb des neuen PostgreSQL-JSON-Datentyps?

Question

Mit postgresql 9.3 kann ich bestimmte Felder eines JSON-Datentyps mit SELECT auswählen, aber wie kann ich sie mit UPDATE ändern? Ich finde keine Beispiele dafür in der postgresql-Dokumentation oder online. Ich habe das Offensichtliche versucht:

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...

Answer 1

Ich fand frühere Antworten eher für erfahrene PostgreSQL-Benutzer geeignet. Dies ist für Anfänger:

Gehen Sie davon aus, dass Sie eine Spaltenspalte vom Typ JSONB mit dem folgenden Wert haben:

{
    "key0": {
        "key01": "2018-05-06T12:36:11.916761+00:00",
        "key02": "DEFAULT_WEB_CONFIGURATION",

    "key1": {
        "key11": "Daten System",
        "key12": "Gesundheit,meine Adresse

Lassen Sie uns davon ausgehen, dass wir einen neuen Wert in der Zeile setzen möchten:

"key13": "*Bitte beachten Sie die Beschriftung auf dem Hauptbildschirm"

und stattdessen den Wert platzieren möchten:

"key13": "Siehe Beschriftung auf dem Hauptbildschirm"

verwenden wir die json_set() Funktion, um einen neuen Wert für den Schlüssel 13 zuzuweisen

die Parameter für jsonb_set()

jsonb_set(Ziel jsonb, Pfad Text[], Neuer Wert jsonb[, Fehlende erstellen boolean])

in "Ziel" - werde ich den jsonb Spaltennamen platzieren (dies ist die Tabellenspalte, die geändert wird)

"Pfad"- ist der "JSON-Schlüsselpfad", der zu dem Schlüssel führt, den wir überschreiben werden

"Neuer Wert" - das ist der neue Wert, den wir zuweisen

In unserem Fall möchten wir den Wert von Schlüssel 13 aktualisieren, der unter Schlüssel 1 (key1 -> key13) liegt:

daher ist die Pfad-Syntax : '{key1,key13}' (Der Pfad war der kniffligste Teil - weil die Tutorials schrecklich sind)

jsonb_set(jsonb_spalte,'{key1,key13}','"Siehe Beschriftung auf dem Hauptbildschirm"')

Answer 2

Sie können versuchen, wie folgt zu aktualisieren:

Syntax: UPDATE table_name SET column_name = column_name::jsonb || '{"key":new_value}' WHERE column_name condition;

Für Ihr Beispiel:

UPDATE test SET data = data::jsonb || '{"a":new_value}' WHERE data->>'b' = '2';

Answer 3

Dies hat für mich funktioniert, als ich versucht habe, ein Feld vom Typ String zu aktualisieren.

UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);

Hoffentlich hilft es auch jemand anderem!

Anzunehmen ist, dass die Tabelle table_name eine Spalte jsonb mit dem Namen body hat und Sie body.some_key auf 'value' ändern möchten

Answer 4

Um auf die Antworten von @pozs aufzubauen, hier sind ein paar weitere PostgreSQL-Funktionen, die für einige nützlich sein könnten. (Erfordert PostgreSQL 9.3+)

Nach Schlüssel löschen: Löscht einen Wert aus der JSON-Struktur nach Schlüssel.

ERSTELLEN ODER ERSETZEN FUNKTION "json_object_del_key"(
  "json"          json,
  "key_to_del"    TEXT
)
  GIBT json ZURÜCK
  SPRACHE sql
  IMMUTABLE
  STRICT
ALS $function$
WÄHLE FALLEN
  WENN ("json" -> "key_to_del") IST NULL DANN "json"
  SONST (WÄHLEN concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          VON (WÄHLEN *
                  VON json_each("json")
                 WO "key" <> "key_to_del"
               ALS "fields")::json
END
$function$;

Rekursives Löschen nach Schlüssel: Löscht einen Wert aus der JSON-Struktur nach Schlüsselpfad. (erfordert @pozs's json_object_set_key Funktion)

ERSTELLEN ODER ERSETZEN FUNKTION "json_object_del_path"(
  "json"          json,
  "key_path"      TEXT[]
)
  GIBT json ZURÜCK
  SPRACHE sql
  IMMUTABLE
  STRICT
ALS $function$
WÄHLE FALLEN
  WENN ("json" -> "key_path"[l] ) IST NULL DANN "json"
  SONST
     WÄHLE COALESCE(array_length("key_path", 1), 0)
         WENN 0 DANN "json"
         WENN 1 DANN "json_object_del_key"("json", "key_path"[l])
         SONST "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
  VON array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

Beispiel für die Verwendung:

s1=# WÄHLE 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"}}

Answer 5

Mit PostgreSQL 9.4 haben wir die folgende Python-Funktion implementiert. Es könnte auch mit PostgreSQL 9.3 funktionieren.

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("Die JSON-Daten müssen ein Objekt oder ein String sein.")

if b.__class__.__name__ != 'list':
   raise plpy.Error("Der JSON-Pfad muss ein Array von Pfaden zum Durchlaufen sein.")

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("Durchlaufen von '" + p + "'")
      if c.__class__.__name__ != 'dict':
        raise plpy.Error("  Der Wert hier ist kein Dictionary.")
      else:
        c = c[p]

    if p.__class__.__name__ == 'int':
      plpy.notice("Durchlaufen von " + str(p))
      if c.__class__.__name__ != 'list':
        raise plpy.Error("  Der Wert hier ist keine Liste.")
      else:
        c = c[p]

    if c is None:
      break    

return json.dumps(a)
$$ language plpython2u ;

Beispielverwendung:

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;

Beachten Sie, dass ich für einen früheren Arbeitgeber eine Reihe von C-Funktionen zum Bearbeiten von JSON-Daten als Text (nicht als json oder jsonb Typ) für PostgreSQL 7, 8 und 9 geschrieben habe. Zum Beispiel das Extrahieren von Daten mit json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']'), das Setzen von Daten mit json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87') und so weiter. Es hat etwa 3 Tage Arbeit gekostet, also wenn Sie es auf Legacy-Systemen ausführen müssen und die Zeit dafür haben, könnte es den Aufwand wert sein. Ich stelle mir vor, dass die C-Version viel schneller als die Python-Version ist.