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?

@A.J.Alger The question is not the same. The value foo can be anywhere in array. — Sahap Asci
– Sahap Asci, Commented Apr 25, 2019 at 9:13

Sahap Asci · Accepted Answer · 2019-04-25 09:22:48Z

4

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)

answered Apr 25, 2019 at 9:22

Sahap Asci

3,2341 gold badge18 silver badges28 bronze badges

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

Chillar Anand
– Chillar Anand

2019-04-26 04:28:50 +00:00
Commented Apr 26, 2019 at 4:28

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

Linked

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions