json - Using jsonb_set() for updating specific jsonb array value

Question

Currently I am working with PostgreSQL 9.5 and try to update a value inside an array of a jsonb field. But I am unable to get the index of the selected value

My table just looks like this:

 CREATE TABLE samples (
    id serial,
    sample jsonb
 );

My JSON looks like this:

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

My SELECT statement to get the correct value works:

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

results in:

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

Ok, this is what I wanted. Now I want to execute an update using the following UPDATE statement to add a new element "ratingtext" (if not already there):

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

After executing the UPDATE statement, my data looks like this (also correct):

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

So far so good, but I manually searched the index value of 2 to get the right element inside the JSON array. If the order will be changed, this won't work.

So my problem:

Is there a way to get the index of the selected JSON array element and combine the SELECT statement and the UPDATE statement into one?

Just like:

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

The values of samples.id and "8410" are known before preparing the statement.

Or is this not possible at the moment?

Answer 1

You can find an index of a searched element using jsonb_array_elements() with ordinality (note, ordinality starts from 1 while the first index of json array is 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) 

Use the above query to update the element based on its index (note that the second argument of jsonb_set() is a text array):

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;    

Result:

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)

The last argument in jsonb_set() should be true to force adding a new value if its key does not exist yet. It may be skipped however as its default value is true.

Though concurrency issues seem to be unlikely (due to the restrictive WHERE condition and a potentially small number of affected rows) you may be also interested in Atomic UPDATE .. SELECT in Postgres.