How to update an element in json array with where clause?

Question

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings.

Here are some sample json values from jsonb column of the table.

{"pattern": {"values": ["foo", "edfgh"]}}
{"pattern": {"values": ["abc", "abdhi", "foo"]}}
{"pattern": {"values": ["these", "abcd", "para", "avil"]}}

We can select the nth element using

select data #> '{pattern, values, 0}' from table;

How can we loop through array and update foo to bar if it is present in that array?

• Sahap Asci: Json values are not valid please correct that
• Chillar Anand: @SahapAsci Corrected it. Thanks.
• Dr G.: Copy of dba.stackexchange.com/questions/193390/…
• Sahap Asci: @A.J.Alger The question is not the same. The value foo can be anywhere in array.
• Sahap Asci: how about if there is more than one "foo" in the array ?

Answer 1

You can use a query like the one below;

UPDATE table SET
 data = jsonb_set(data,'{pattern, values}', ((data->'pattern'->'values') - 'foo') || '["bar"]' )
WHERE
 (data #> '{pattern, values}') ? 'foo';

Details;

First you have to find records which have "foo" value in "values" array;

(data #> '{pattern, values}') ? 'foo'

After that remove the value "foo" from the array;

((data->'pattern'->'values') - 'foo')

Create a new array by concatenating your new value(s).

((data->'pattern'->'values') - 'foo') || '["bar"]'

Replace new array with the old one.

jsonb_set(data,'{pattern, values}', new_array)

• Chillar Anand: This replaces the element but doesn't preserve the order. Is there a way to update it and preserve order also?