Postgresql – How to update an element in json array with where clause

jsonpostgresqlupdate

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?

Best Answer

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)

Related Solutions

PostgreSQL, finding elements by value in numeric JSON arrays

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT
  j,
  j @> '"dog"'::jsonb AS hasDog,
  j @> '["dog","waffle"]' AS hasDogAndWaffle,
  j @> '5'   AS has5,
  j @> '42'  AS has42
FROM ( VALUES
  ('[5,2,3]'::jsonb),
  ('["dog","cat","elephant","waffle"]'::jsonb)
)
  AS t(j);

                  j                   | hasdog | hasdogandwaffle | has5 | has42 
--------------------------------------+--------+-----------------+------+-------
 [5, 2, 3]                            | f      | f               | t    | f
 ["dog", "cat", "elephant", "waffle"] | t      | t               | f    | f
(2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.

PostgreSQL – Query JSON Array of Objects Against Multiple Values

There is a way: combine the containment operator @> with the ANY construct:

SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.

You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?

Best Answer

Related Solutions

PostgreSQL, finding elements by value in numeric JSON arrays

PostgreSQL – Query JSON Array of Objects Against Multiple Values

Related Question