Comment modifier les champs du nouveau type de données JSON PostgreSQL?

avec 9.5 utiliser jsonb_set -

UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1; 

où le corps est de type colonne jsonb.

avec Postgresql 9.5 il peut être fait en suivant -

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

ou

UPDATE test
SET data = jsonb_set(data, '{a}', '5'::jsonb);

Quelqu'un a demandé comment mettre à jour plusieurs champs dans la valeur jsonb à la fois. Supposons que nous créons une table:

CREATE TABLE testjsonb ( id SERIAL PRIMARY KEY, object JSONB );

ensuite nous insérons une ligne expérimentale:

INSERT INTO testjsonb
VALUES (DEFAULT, '{"a":"one", "b":"two", "c":{"c1":"see1","c2":"see2","c3":"see3"}}');

puis nous mettons à jour la ligne:

UPDATE testjsonb SET object = object - 'b' || '{"a":1,"d":4}';

qui fait ce qui suit:

1. met à jour le champ
2. supprime le champ b
3. ajouter le champ d

sélection des données:

SELECT jsonb_pretty(object) FROM testjsonb;

résultera en:

      jsonb_pretty
-------------------------
 {                      +
     "a": 1,            +
     "c": {             +
         "c1": "see1",  +
         "c2": "see2",  +
         "c3": "see3",  +
     },                 +
     "d": 4             +
 }
(1 row)

pour mettre à jour le champ intérieur, N'utilisez pas l'opérateur concat || . Utilisez plutôt jsonb_set. Ce qui n'est pas simple:

UPDATE testjsonb SET object =
jsonb_set(jsonb_set(object, '{c,c1}','"seeme"'),'{c,c2}','"seehim"');

à l'aide de la opérateur pour {c, c1} par exemple:

UPDATE testjsonb SET object = object || '{"c":{"c1":"seedoctor"}}';

supprime {c, c2} et {c, c3}.

Pour plus de puissance, de chercher le pouvoir à postgresql json documentation des fonctions . On pourrait s'intéresser à l'opérateur #- , à la fonction jsonb_set et aussi à la fonction jsonb_insert .

pour construire sur les réponses de @pozs, voici quelques fonctions PostgreSQL supplémentaires qui peuvent être utiles à certains. (Nécessite PostgreSQL 9.3+)

supprimer par la touche: supprime une valeur de la structure JSON par la touche.

CREATE OR REPLACE FUNCTION "json_object_del_key"(
  "json"          json,
  "key_to_del"    TEXT
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_del") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_del"
               ) AS "fields")::json
END
$function$;

suppression récursive par touche: supprime une valeur de la structure JSON par chemin de touche. (nécessite la fonction json_object_set_key de @pozs)

CREATE OR REPLACE FUNCTION "json_object_del_path"(
  "json"          json,
  "key_path"      TEXT[]
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
  ELSE
     CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN "json"
         WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
         ELSE "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
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

Exemples d'utilisation:

s1=# SELECT 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"}}

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

cela semble fonctionner sur PostgreSQL 9.5

avec PostgreSQL 9.4, nous avons implémenté la fonction python suivante. Il peut également fonctionner avec PostgreSQL 9.3.

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("The json data must be an object or a string.")

if b.__class__.__name__ != 'list':
   raise plpy.Error("The json path must be an array of paths to traverse.")

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("Traversing '" + p + "'")
      if c.__class__.__name__ != 'dict':
        raise plpy.Error("  The value here is not a dictionary.")
      else:
        c = c[p]

    if p.__class__.__name__ == 'int':
      plpy.notice("Traversing " + str(p))
      if c.__class__.__name__ != 'list':
        raise plpy.Error("  The value here is not a list.")
      else:
        c = c[p]

    if c is None:
      break    

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

exemple d'usage:

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;

notez que pour un employeur précédent, j'ai écrit un ensemble de fonctions C pour manipuler des données JSON sous forme de texte (pas de type json ou jsonb ) pour PostgreSQL 7, 8 et 9. Par exemple , extraire des données avec json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']') , définir des données avec json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87') et ainsi de suite. Il cela a pris environ 3 jours de travail, donc si vous en avez besoin pour fonctionner sur les systèmes existants et avoir le temps d'épargner, cela peut valoir la peine de l'effort. J'imagine que la version C est beaucoup plus rapide que la version python.

même si ce qui suit ne satisfera pas cette demande (la fonction json_object_agg n'est pas disponible dans PostgreSQL 9.3), ce qui suit peut être utile pour quiconque cherche un opérateur | pour PostgreSQL 9.4, tel que mis en œuvre dans le PostgreSQL 9.5 à venir:

CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB)
RETURNS JSONB
AS $$
SELECT
  CASE WHEN jsonb_typeof() = 'object' AND jsonb_typeof() = '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() o
        FULL JOIN jsonb_each() n ON (n.key = o.key))
   ELSE 
     (CASE WHEN jsonb_typeof() = 'array' THEN LEFT(::text, -1) ELSE '['||::text END ||', '||
      CASE WHEN jsonb_typeof() = 'array' THEN RIGHT(::text, -1) ELSE ::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 );

j'ai écrit une petite fonction pour moi-même qui fonctionne récursivement dans Postgres 9.4. Voici la fonction (j'espère que cela fonctionne bien pour vous):

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;

voici un exemple d'utilisation:

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)

comme vous pouvez le voir analyser en profondeur et mettre à jour/ajouter des valeurs si nécessaire.

malheureusement, je n'ai rien trouvé dans la documentation, mais vous pouvez utiliser une solution de contournement, par exemple vous pouvez écrire une fonction étendue.

par exemple, en Python:

CREATE or REPLACE FUNCTION json_update(data json, key text, value json)
returns json
as $$
from json import loads, dumps
if key is None: return data
js = loads(data)
js[key] = value
return dumps(js)
$$ language plpython3u

et ensuite

update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';

l'extrait suivant de plpython pourrait s'avérer utile.

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;

-- Check how JSON looks before updating

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

-- Once satisfied update JSON inplace

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

si votre type de champ est de json, ce qui suit fonctionnera pour vous.

UPDATE 
table_name
SET field_name = field_name::jsonb - 'key' || '{"key":new_val}' 
WHERE field_name->>'key' = 'old_value'.

Opérateur " - " supprimer paire clé/valeur ou élément de chaîne à partir de l'opérande gauche. Les couples clé/valeur sont appariés en fonction de leur valeur clé.

Opérateur "| | " à la concaténation de deux jsonb des valeurs dans une nouvelle jsonb valeur.

Puisqu'il s'agit d'opérateurs jsonb, vous n'avez qu'à taper::jsonb "151990920