postgresql 9.5 utilise jsonb_set pour mettre à jour une valeur spécifique d'un tableau jsonb

Question

Je travaille actuellement avec postgreSQL 9.5 et j'essaie de mettre à jour une valeur à l'intérieur d'un tableau d'un champ jsonb. Mais je n'arrive pas à obtenir l'index de la valeur sélectionnée.

Ma table ressemble à ceci :

 CREATE TABLE samples (
    id serial,
    sample jsonb
 );

Mon JSON ressemble à ceci :

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin"}
]}

Mon instruction SELECT pour obtenir la valeur correcte fonctionne :

SELECT 
    id, value 
FROM 
    samples s, jsonb_array_elements(s.sample#>'{result}') r  
WHERE 
    s.id = 26 and r->>'8410' = 'FERR_R';

se traduit par des résultats :

id | value
----------------------------------------------
26 | {"8410": "FERR_R", "8411": "Ferritin"}

Ok, c'est ce que je voulais. Je veux maintenant exécuter une mise à jour en utilisant l'instruction UPDATE suivante pour ajouter un nouvel élément "ratingtext" (s'il n'existe pas déjà) :

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,2,ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

Après avoir exécuté l'instruction UPDATE, mes données ressemblent à ceci (également correctes) :

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin", "ratingtext": "Some individual text"}
]}

Jusqu'à présent, tout va bien, mais je manuellement a recherché la valeur d'index 2 pour obtenir le bon élément dans le tableau JSON. Si l'ordre est modifié, cela ne fonctionnera pas.

C'est donc mon problème :

Existe-t-il un moyen d'obtenir l'index de l'élément de tableau JSON sélectionné et de combiner l'instruction SELECT et l'instruction UPDATE en une seule ?

Tout comme :

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,' || INDEX OF ELEMENT || ',ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

Les valeurs de échantillons.id y "8410" sont connues avant de préparer la déclaration.

Ou cela n'est-il pas possible pour le moment ?

Answer 1

Vous pouvez trouver l'index d'un élément recherché à l'aide de la fonction jsonb_array_elements() with ordinality (note, ordinality commence à 1 alors que le premier index du tableau json est 0) :

select 
    pos- 1 as elem_index
from 
    samples, 
    jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
where
    id = 26 and
    elem->>'8410' = 'FERR_R';

 elem_index 
------------
          2
(1 row) 

Utilisez la requête ci-dessus pour mettre à jour l'élément sur la base de son index (notez que le deuxième argument de la commande jsonb_set() est un tableau de texte) :

update 
    samples
set
    sample = 
        jsonb_set(
            sample,
            array['result', elem_index::text, 'ratingtext'],
            '"some individual text"'::jsonb,
            true)
from (
    select 
        pos- 1 as elem_index
    from 
        samples, 
        jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
    where
        id = 26 and
        elem->>'8410' = 'FERR_R'
    ) sub
where
    id = 26;    

Résultat :

select id, jsonb_pretty(sample)
from samples;

 id |                   jsonb_pretty                   
----+--------------------------------------------------
 26 | {                                               +
    |     "result": [                                 +
    |         {                                       +
    |             "8410": "ABNDAT",                   +
    |             "8411": "Abnahmedatum"              +
    |         },                                      +
    |         {                                       +
    |             "8410": "ABNZIT",                   +
    |             "8411": "Abnahmezeit"               +
    |         },                                      +
    |         {                                       +
    |             "8410": "FERR_R",                   +
    |             "8411": "Ferritin",                 +
    |             "ratingtext": "Some individual text"+
    |         }                                       +
    |     ]                                           +
    | }
(1 row)

Le dernier argument de jsonb_set() devrait être true pour forcer l'ajout d'une nouvelle valeur si sa clé n'existe pas encore. Il peut cependant être ignoré car sa valeur par défaut est true .

Bien que les problèmes de concurrence semblent peu probables (en raison de la condition restrictive WHERE et d'un nombre potentiellement faible de lignes affectées), vous pouvez également être intéressé par les éléments suivants UPDATE atomique SELECT dans Postgres.