current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 3 down vote favorite
1

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?

share|improve this question
up vote 2 down vote accepted

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)
share|improve this answer
    
Thank you very much for your answer! It works like a charme! I did not realize, that I can use the elem in the WHERE clause. – Daniel Seichter Aug 17 at 13:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.