Using jsonb_set() for updating specific jsonb array value
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.
Related videos on Youtube
Daniel Seichter
20 years of experiences in software development, some of the projects were succeedded, some of them are failed, but in each project, I learned more and more about good code, bad practices, what is possible, what should be impossible (but was possible, because of customers want it).
Updated on July 09, 2022Comments
-
Daniel Seichter almost 2 years
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?
-
Daniel Seichter over 7 yearsThank 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.
-
harsh tibrewal over 6 yearsThanks klin, this just saved my day !