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?

  • Json values are not valid please correct that
    – Sahap Asci
    Apr 24, 2019 at 20:24
  • @SahapAsci Corrected it. Thanks. Apr 25, 2019 at 7:04
  • Copy of dba.stackexchange.com/questions/193390/…
    – Dr G.
    Apr 25, 2019 at 7:21
  • @A.J.Alger The question is not the same. The value foo can be anywhere in array.
    – Sahap Asci
    Apr 25, 2019 at 9:13
  • how about if there is more than one "foo" in the array ?
    – Sahap Asci
    Apr 29, 2019 at 10:54

1 Answer 1


You can use a query like the one below;

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


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)
  • This replaces the element but doesn't preserve the order. Is there a way to update it and preserve order also? Apr 26, 2019 at 4:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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