Daniel Seichter Daniel Seichter - 3 months ago 54
JSON Question

postgresql 9.5 jsonb_set getting index of json array

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:

CREATE TABLE samples (
id serial,
sample jsonb
);


My JSON looks like:

{"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 execute 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

You can find an index of a searched element using jsonb_array_elements() with ordinality:

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