postgresql 9.5 using jsonb_set to update a specific jsonb array value

Question

I am currently working with postgreSQL 9.5 and am trying to update a value inside a jsonb field array. But I can not get the index of the selected value

My table looks simple:

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'; 

leads to:

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

Ok, this is what I wanted. Now I want to upgrade using the following UPDATE statement to add a new "ratingtext" element (if it does not already exist):

 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 for the value of index 2 to get the right element inside the JSON array. If the order is changed, this will not work.

So my problem is:

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

As well as:

 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 it impossible at the moment?

Answer 1

You can find the index of jsonb_array_elements() with ordinality element using jsonb_array_elements() with ordinality (note, ordinality starts at 1, and the first index of the 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 an element based on its index (note that the second argument to 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 final argument to jsonb_set() must be true to force a new value to be added if its key does not already exist. However, it may be skipped because its default value is true .

Although concurrency issues seem unlikely (due to the WHERE clause and the potentially small number of rows affected), you might also be interested in Atomic UPDATE .. SELECT in Postgres.