0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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