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

Sie können Schlüssel auch atomar innerhalb von jsonb wie folgt erhöhen:

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}

Undefinierter Schlüssel -> Annahme eines Startwerts von 0.

Für eine ausführlichere Erklärung siehe meine Antwort hier: https://stackoverflow.com/a/39076637

Answer 2

Auch wenn das Folgende diese Anforderung nicht erfüllen wird (die Funktion json_object_agg ist in PostgreSQL 9.3 nicht verfügbar), kann das Folgende für alle nützlich sein, die nach einem || Operator für PostgreSQL 9.4 suchen, wie er in dem kommenden PostgreSQL 9.5 implementiert wird:

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

Answer 3

Ich habe für mich selbst eine kleine Funktion geschrieben, die in Postgres 9.4 rekursiv funktioniert. Hier ist die Funktion (ich hoffe, sie funktioniert gut für dich):

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;

Hier ist eine Beispielverwendung:

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)

Wie Sie sehen können, analysiert es tiefgreifend und aktualisiert/hinzufügt Werte, wo es benötigt wird.

Answer 4

Diese Lösung ist eine Alternative zu jsonb_set, die auch funktioniert, wenn die Spalte NULL für das JSONB enthält. Das jsonb_set funktioniert nur, wenn das Objekt existiert.

In dem folgenden Beispiel ist "settings" eine JSONB-Spalte in der Benutzertabelle.

 UPDATE public."Users"
 SET settings = coalesce("settings", '{}')::jsonb || '{ "KeyToSet" : "ValueToSet" }'
 WHERE id=35877;

Answer 5

Der folgende plpython-Schnipsel könnte nützlich sein.

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;

-- Überprüfen, wie JSON aussieht, bevor es aktualisiert wird

SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis';

-- Sobald zufrieden, JSON an Ort und Stelle aktualisieren

UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
WHERE record_id = 35 AND template = 'CFRDiagnosis';