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

Avec postgresql 9.3, je peux CHOISIR des champs spécifiques d’un type de données JSON, mais comment les modifier avec UPDATE? Je ne trouve aucun exemple de ceci dans la documentation postgresql, ou n’importe où en ligne. J’ai essayé l’évidence:

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

    Mise à jour : Avec PostgreSQL 9.5 , il existe des fonctionnalités de manipulation jsonb dans PostgreSQL jsonb lui-même (mais aucune pour json ; les moulages sont nécessaires pour manipuler les valeurs json ).

    Fusion de 2 (ou plus) objects JSON (ou tableaux concaténants):

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

    Ainsi, vous pouvez définir une clé simple en utilisant:

     SELECT jsonb '{"a":1}' || jsonb_build_object('', '') 

    devrait être ssortingng, et peut être n’importe quel type to_jsonb() .

    Pour définir une valeur profonde dans une hiérarchie JSON , la fonction jsonb_set() peut être utilisée:

     SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}') -- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}' 

    Liste complète des parameters de jsonb_set() :

     jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean default true) 

    path peut contenir des index de tableau JSON et des nombres entiers négatifs qui apparaissent à la fin des tableaux JSON. Cependant, un index de tableau JSON non existant, mais positif, appenda l’élément à la fin du tableau:

     SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true) -- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}' 

    Pour l’ insertion dans un tableau JSON (tout en conservant toutes les valeurs d’origine) , la fonction jsonb_insert() peut être utilisée ( dans 9.6+; cette fonction uniquement, dans cette section ):

     SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2') -- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true) -- will yield jsonb '{"a":[null,{"b":[1,2]}]}' 

    Liste complète des parameters de jsonb_insert() :

     jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean default false) 

    Là encore, les nombres entiers négatifs qui apparaissent dans le path comptés à partir de la fin des tableaux JSON.

    Donc, f.ex. append à une fin d’un tableau JSON peut être fait avec:

     SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true) -- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and 

    Cependant, cette fonction fonctionne légèrement différemment (que jsonb_set() ) lorsque le path dans target est la clé d’un object JSON. Dans ce cas, il appenda uniquement une nouvelle paire clé-valeur pour l’object JSON lorsque la clé n’est pas utilisée. Si elle est utilisée, cela provoquera une erreur:

     SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]') -- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]') -- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key 

    La suppression d’une clé (ou d’un index) à partir d’un object JSON (ou d’un tableau) peut être effectuée avec l’opérateur - :

     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 suppression, à partir d’une profondeur dans une hiérarchie JSON, peut être effectuée avec l’opérateur #- :

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

    Pour 9.4 , vous pouvez utiliser une version modifiée de la réponse d’origine (ci-dessous), mais au lieu d’agréger une chaîne JSON, vous pouvez l’agréger directement dans un object json avec json_object_agg() .

    Réponse originale : C’est possible (sans plpython ou plv8) en SQL pur (mais nécessite 9.3+, ne fonctionnera pas avec 9.2)

     CREATE OR REPLACE FUNCTION "json_object_set_key"( "json" json, "key_to_set" TEXT, "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT concat('{', ssortingng_agg(to_json("key") || ':' || "value", ','), '}')::json FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_set" UNION ALL SELECT "key_to_set", to_json("value_to_set")) AS "fields" $function$; 

    SQLFiddle

    Modifier :

    Une version qui définit plusieurs clés et valeurs:

     CREATE OR REPLACE FUNCTION "json_object_set_keys"( "json" json, "keys_to_set" TEXT[], "values_to_set" anyarray ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT concat('{', ssortingng_agg(to_json("key") || ':' || "value", ','), '}')::json FROM (SELECT * FROM json_each("json") WHERE "key" <> ALL ("keys_to_set") UNION ALL SELECT DISTINCT ON ("keys_to_set"["index"]) "keys_to_set"["index"], CASE WHEN "values_to_set"["index"] IS NULL THEN 'null'::json ELSE to_json("values_to_set"["index"]) END FROM generate_subscripts("keys_to_set", 1) AS "keys"("index") JOIN generate_subscripts("values_to_set", 1) AS "values"("index") USING ("index")) AS "fields" $function$; 

    Edit 2 : comme @ErwinBrandstetter a noté ces fonctions ci-dessus fonctionne comme un soi-disant UPSERT (met à jour un champ s’il existe, insère s’il n’existe pas). Voici une variante, qui ne fait que mettre à jour:

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

    Edit 3 : Voici la variante récursive, qui peut définir ( UPSERT ) une valeur de feuille (et utilise la première fonction de cette réponse), située sur un chemin de clé (où les clés ne peuvent se référer qu’à des objects internes, les tableaux internes non pris en charge):

     CREATE OR REPLACE FUNCTION "json_object_set_path"( "json" json, "key_path" TEXT[], "value_to_set" anyelement ) RETURNS json LANGUAGE sql IMMUTABLE STRICT AS $function$ SELECT CASE COALESCE(array_length("key_path", 1), 0) WHEN 0 THEN to_json("value_to_set") WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set") ELSE "json_object_set_key"( "json", "key_path"[l], "json_object_set_path"( COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json, "key_path"[l+1:u], "value_to_set" ) ) END FROM array_lower("key_path", 1) l, array_upper("key_path", 1) u $function$; 

    Mise à jour : les fonctions sont compactées maintenant.

    Avec 9.5, utilisez jsonb_set-

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

    où body est un type de colonne jsonb.

    Avec Postgresql 9.5, cela 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 de la valeur jsonb à la fois. Supposons que nous créons un tableau:

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

    Ensuite, 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; 

    Aura pour résultat:

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

    Pour mettre à jour le champ à l’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"'); 

    En utilisant l’opérateur concat pour {c, c1} par exemple:

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

    Va supprimer {c, c2} et {c, c3}.

    Pour plus de puissance, recherchez la puissance sur la documentation des fonctions postgresql json . On pourrait être intéressé par l’opérateur #- , la fonction jsonb_set et aussi la fonction jsonb_insert .

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

    Delete By Key: supprime une valeur de la structure JSON par clé.

     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('{', ssortingng_agg(to_json("key") || ':' || "value", ','), '}') FROM (SELECT * FROM json_each("json") WHERE "key" <> "key_to_del" ) AS "fields")::json END $function$; 

    Supprimer par clé récursive: supprime une valeur de la structure JSON par chemin de clé. (nécessite la fonction json_object_set_key @ 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 ssortingng.") 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’utilisation:

     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éfinissant des données avec json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87') et ainsi de suite. Cela a pris environ 3 jours de travail, donc si vous en avez besoin pour fonctionner sur des systèmes hérités et que vous en avez le temps, cela en vaut la peine. J’imagine que la version C est beaucoup plus rapide que la version python.

    Même si les éléments suivants ne satisferont pas cette requête (la fonction json_object_agg n’est pas disponible dans PostgreSQL 9.3), les éléments suivants peuvent être utiles pour ceux qui recherchent un || opérateur pour PostgreSQL 9.4, implémenté dans le prochain PostgreSQL 9.5:

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

    J’ai écrit une petite fonction pour moi 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, parsingz en profondeur et mettez à jour / ajoutez 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 pourriez é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 alors

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

    L’extrait de plpython suivant pourrait être 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 ‘-‘ supprime la paire clé / valeur ou l’élément chaîne de l’opérande gauche. Les paires clé / valeur sont appariées en fonction de leur valeur de clé.

    Opérateur ‘||’ concaténer deux valeurs jsonb dans une nouvelle valeur jsonb.

    Puisque ce sont des opérateurs jsonb, il vous suffit de passer à :: jsonb

    Plus d’infos: Fonctions et opérateurs JSON

    Vous pouvez lire ma note ici

    Vous pouvez également incrémenter les clés de manière atomique dans jsonb comme ceci:

     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} 

    Clé indéfinie -> suppose une valeur de départ de 0.

    Pour plus d’explications, voir ma réponse ici: https://stackoverflow.com/a/39076637

    Cela a fonctionné pour moi, en essayant de mettre à jour un champ de type chaîne.

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

    J’espère que ça aide quelqu’un d’autre!