Comment effectuer des opérations de mise à jour sur des colonnes de type JSONB dans Postgres 9.4

En parcourant la documentation du type de données Postgres 9.4 JSONB, il ne m’est pas immédiatement évident de savoir comment faire des mises à jour sur les colonnes JSONB.

Documentation pour les types et fonctions JSONB:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

A titre d’exemple, j’ai cette structure de table de base:

CREATE TABLE test(id serial, data jsonb); 

L’insertion est facile, comme dans:

 INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}'); 

Maintenant, comment pourrais-je mettre à jour la colonne ‘data’? Ceci est la syntaxe non valide:

 UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1; 

Est-ce documenté quelque part évident que j’ai manqué? Merci.

Idéalement, vous n’utilisez pas de documents JSON pour les données que vous souhaitez gérer dans une firebase database relationnelle. Utilisez plutôt une conception relationnelle normalisée .

JSON est principalement destiné à stocker des documents entiers qui n’ont pas besoin d’être manipulés à l’intérieur du SGBDR. En relation:

  • JSONB avec indexation vs hstore

La mise à jour d’une ligne dans Postgres écrit toujours une nouvelle version de la ligne entière . C’est le principe de base du modèle MVCC de Postgres . Du sharepoint vue des performances, peu importe si vous modifiez un seul élément de données dans un object JSON ou la totalité: une nouvelle version de la ligne doit être écrite.

Ainsi le conseil dans le manuel :

Les données JSON sont soumises aux mêmes considérations de contrôle de la concurrence que tout autre type de données lorsqu’elles sont stockées dans une table. Bien que le stockage de documents volumineux soit possible, gardez à l’esprit que toute mise à jour acquiert un verrou de niveau ligne sur toute la ligne. Envisagez de limiter les documents JSON à une taille gérable afin de réduire les conflits de locking entre les transactions de mise à jour. Idéalement, les documents JSON devraient chacun représenter une donnée atomique que les règles métier imposent ne peuvent pas être subdivisées en de plus petites informations pouvant être modifiées indépendamment.

L’essentiel: pour modifier quoi que ce soit à l’ intérieur d’un object JSON, vous devez affecter un object modifié à la colonne. Postgres fournit des moyens limités pour créer et manipuler des données json en plus de ses capacités de stockage. L’arsenal des outils a considérablement augmenté avec chaque nouvelle version depuis la version 9.2. Mais le principal rest: vous devez toujours affecter un object modifié complet à la colonne et Postgres écrit toujours une nouvelle version de ligne pour toute mise à jour.

Quelques techniques pour travailler avec les outils de Postgres 9.3 ou ultérieur:

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

Si vous pouvez effectuer une mise à niveau vers Postgresql 9.5, la commande jsonb_set est disponible, comme d’autres l’ont mentionné.

Dans chacune des instructions SQL suivantes, j’ai omis la clause where pour des raisons de brièveté; de toute évidence, vous voudriez append cela.

Nom de la mise à jour:

 UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"'); 

Remplacez les balises (opposées à l’ajout ou à la suppression de balises):

 UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]'); 

Remplacement de la deuxième balise (indexée sur 0):

 UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"'); 

Ajoutez une balise ( cela fonctionnera tant qu’il y aura moins de 999 balises; changer l’argument 999 en 1000 ou plus génère une erreur . Cela ne semble plus être le cas dans Postgres 9.5.3; un index beaucoup plus grand peut être utilisé) :

 UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true); 

Supprimer la dernière balise:

 UPDATE test SET data = data #- '{tags,-1}' 

Mise à jour complexe (supprimez la dernière balise, insérez une nouvelle balise et modifiez le nom):

 UPDATE test SET data = jsonb_set( jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), '{name}', '"my-other-name"'); 

Il est important de noter que dans chacun de ces exemples, vous ne mettez pas à jour un seul champ des données JSON. Au lieu de cela, vous créez une version temporaire modifiée des données et vous atsortingbuez cette version modifiée à la colonne. En pratique, le résultat devrait être le même, mais garder cela à l’esprit devrait rendre les mises à jour complexes, comme le dernier exemple, plus compréhensibles.

Dans l’exemple complexe, il existe trois transformations et trois versions temporaires: Tout d’abord, la dernière balise est supprimée. Ensuite, cette version est transformée en ajoutant une nouvelle balise. Ensuite, la deuxième version est transformée en changeant le champ du name . La valeur dans la colonne de data est remplacée par la version finale.

Ceci arrive dans 9.5 sous la forme de jsonb_set par Andrew Dunstan basé sur une extension existante jsonbx qui fonctionne avec 9.4

Cette question a été posée dans le contexte de postgres 9.4, cependant, les nouveaux spectateurs qui abordent cette question doivent savoir que, dans postgres 9.5, les opérations de création / mise à jour / suppression des sous-documents sur les champs JSONB sont sockets en charge en mode natif. les fonctions.

Voir: JSONB modifiant les opérateurs et les fonctions

Pour ceux qui rencontrent ce problème et veulent une solution très rapide (et sont bloqués sur 9.4.5 ou plus tôt), voici ce que j’ai fait:

Création de table de test

 CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}'); 

Instruction de mise à jour pour changer le nom de la propriété jsonb

 UPDATE test SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb WHERE id = 1; 

En fin de compte, la réponse acceptée est correcte en ce sens que vous ne pouvez pas modifier un élément individuel d’un object jsonb (au 9.4.5 ou antérieur); Cependant, vous pouvez convertir l’object jsonb en une chaîne (:: TEXT), puis manipuler la chaîne et la convertir en l’object jsonb (:: jsonb).

Il y a deux mises en garde importantes

  1. cela remplacera toutes les propriétés appelées “name” dans json (dans le cas où vous avez plusieurs propriétés avec le même nom)
  2. Ce n’est pas aussi efficace que jsonb_set serait si vous utilisez 9.5

Cela étant dit, je suis tombé sur une situation où je devais mettre à jour le schéma du contenu dans les objects jsonb et c’était la manière la plus simple d’accomplir exactement ce que demandait l’affiche originale.

J’ai écrit une petite fonction pour moi qui fonctionne récursivement dans Postgres 9.4. J’ai eu le même problème (bon ils ont résolu une partie de cette migraine dans Postgres 9.5). En tout cas 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.

Peut-être: UPDATE test SET data = ‘”my-other-name”‘ :: json WHERE id = 1;

Cela a fonctionné avec mon cas, où les données sont de type json

Matheus de Oliveira a créé des fonctions pratiques pour les opérations JSON CRUD en postgresql. Ils peuvent être importés en utilisant la directive \ i. Notez le fork jsonb des fonctions si jsonb si votre type de données.

9,3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282