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

en parcourant la documentation pour le type de données JSONB Postgres 9.4, il ne me semble pas évident de 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

comme un exemple, j'ai cette base structure du tableau:

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 mettre à jour la colonne "données"? Cette syntaxe n'est pas valide:

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

est-ce documenté quelque part que j'ai raté? Grâce.

67
demandé sur Houari 2014-11-02 22:37:24

8 réponses

Idéalement, vous n'utilisez pas les documents JSON pour les données que vous voulez gérer à l'intérieur d'une base de données 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. Related:

mise à jour d'une ligne dans Postgres toujours écrit une nouvelle version de la entier rangée. C'est le principe de base de Postgres' MVCC model . Du point de vue de la performance, peu importe que vous changiez une seule donnée à l'intérieur d'un objet JSON ou la totalité: une nouvelle version de la rangée doit être écrite.

ainsi le conseil dans le manuel :

données JSON est soumis à la même concurrence-Considérations relatives au contrôle tout autre type de données lorsqu'elles sont stockées dans une table. Bien que le stockage de grandes documents est réalisable, garder à l'esprit que toute mise à jour acquiert un verrouillage de niveau de rangée sur toute la rangée. Envisager de limiter les documents JSON à une taille gérable afin de diminuer de verrouillage entre la mise à jour transaction. Idéalement, les documents JSON devraient représenter chacun un atome. données que les règles commerciales dictent ne peut raisonnablement être plus réparties en petites références qui pourraient être modifié indépendamment.

L'essentiel, c': modifier rien à l'intérieur d'un objet JSON, vous devez affecter un objet modifié à la colonne. Postgres supplies limited signifie construire et manipuler des données json en plus de ses capacités de stockage. L'arsenal d'outils s'est considérablement étoffé avec chaque nouvelle version depuis la version 9.2. Mais le principal reste: vous toujours doivent attribuer un complète objet modifié pour la colonne et Postgres toujours écrit une nouvelle version de ligne pour toute mise à jour.

quelques techniques pour travailler avec les outils de Postgres 9.3 ou plus tard:

25
répondu Erwin Brandstetter 2018-04-07 02:33:22

si vous pouvez passer à Postgresql 9.5, la commande jsonb_set est disponible, comme d'autres l'ont mentionné.

dans chacune des déclarations SQL suivantes, j'ai omis la clause where pour la brièveté; évidemment, vous voudriez ajouter cela en arrière.

mettre à Jour le nom de:

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

remplacer les étiquettes (par opposition à l'ajout ou à la suppression d'étiquettes):

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

remplaçant la deuxième étiquette (0-indexé):

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

ajoute une balise ( cela fonctionnera aussi longtemps qu'il y aura moins de 999 tags; 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 indice beaucoup plus grand peut être utilisé):

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

supprimer la dernière étiquette:

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

mise à jour Complexe (supprimer la dernière étiquette, insérer une nouvelle étiquette, et changer 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 assignez cette version modifiée de nouveau à la colonne. Dans la 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 y a trois transformations et trois versions temporaires: tout d'abord, la dernière balise est supprimée. Puis, cette version est transformée en ajoutant une nouvelle étiquette. Ensuite, la deuxième version est transformée en changeant le champ name . La valeur dans la colonne data est remplacée par la version finale.

150
répondu Jimothy 2017-06-27 17:13:21

cela vient en 9.5 sous la forme de jsonb_set par Andrew Dunstan basé sur une extension existante jsonbx qui ne fonctionne avec 9.4

22
répondu philofinfinitejest 2015-07-08 21:10:20

Cette question a été posée dans le contexte de postgresql 9.4, cependant, les nouveaux téléspectateurs qui viennent à cette question devraient être conscients que dans Postgres 9.5, sous-document Créer/mettre à jour / supprimer les opérations sur les champs JSONB sont nativement pris en charge par la base de données, sans avoir besoin de fonctions d'extension.

Voir: JSONB la modification des opérateurs et des fonctions

9
répondu bguiz 2015-08-18 07:46:51

pour ceux qui se trouvent dans cette question et qui veulent une solution très rapide (et qui sont bloqués sur le 9.4.5 ou plus tôt), voici ce que j'ai fait:

création d'une table d'essai

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

mise à Jour de la déclaration de changement de nom de jsonb propriété

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

finalement, la réponse acceptée est correcte en ce que vous ne pouvez pas modifier une pièce individuelle d'un objet jsonb (en 9.4.5 ou plus tôt); cependant, vous pouvez lancer l'objet jsonb vers une chaîne de caractères (::TEXT), puis manipuler la chaîne de caractères et renvoyer l'objet jsonb à l'objet jsonb (: jsonb).

il y a deux mises en garde importantes

  1. cela remplacera toutes les propriétés appelées" name "dans le 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 dit, je suis tombé sur une situation où j'ai dû mettre à jour le schéma pour le contenu dans les objets jsonb et c'était la façon la plus simple d'accomplir exactement ce que l'affiche originale demandait.

9
répondu Chad Capra 2018-07-05 16:02:35

j'ai écrit une petite fonction pour moi-même qui fonctionne récursivement dans Postgres 9.4. J'ai eu le même problème (bon ils ont résolu certains de ces maux de tête dans Postgres 9.5). Quoi qu'il en soit 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.

4
répondu J. Raczkiewicz 2016-10-13 12:05:18

peut-être: UPDATE test SET data = '"my-other-name"':: JSON WHERE id = 1;

il a fonctionné avec mon cas, où les données est un type json

3
répondu Gianluigi Sartori 2015-10-21 08:27:53

Matheus de Oliveira a créé des fonctions pratiques pour les opérations JSON CRUD à postgresql. Ils peuvent être importés en utilisant la directive \I. Remarquez la fourche 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

2
répondu John Clark 2015-06-06 10:55:19