How to do a lookup for array elements in jsonb field?

Question

Let's say I have a table:

SELECT * FROM settings;
| id | name    | strategies |
| -- | ---     | ---        |
| 1  | default | [{name: xyz, enabled: true}, {name: bot, enabled: true}]  |
| 2  | new1    | [{name: bot, enabled: true}, {name: xyz, enabled: false}] |

strategies here is a jsonb type field (an array of objects).

I want to change the value of one property in one element (object) in strategies column (array) - i.e. rename "bot" to "bot2".

I figured I can do it by:

-- renames strategy bot to bot2 using fixed index
UPDATE settings
SET strategies = jsonb_set(strategies, '{1}', '{
  "name": "bot2",
  "enabled": true,
}', FALSE)
WHERE name = 'default';

But I don't like it is using a magic number for array index ({1}).

What if I don't know the index of the array element to be edited (or if the index is not the same for all records)? How can I perform a lookup on array elements in jsonb field based on its property, i.e. look for name='bot'? I'm using PostgreSQL v10.5.

Pretty much the same as this: https://stackoverflow.com/a/63010218 — , Jul 21 '20 at 10:38
You'll want to [use a subquery with `jsonb_array_elements` and `jsonb_agg`](https://stackoverflow.com/a/58924070/1048572) — Bergi, Jul 21 '20 at 11:15
**or** [this](https://stackoverflow.com/questions/62844583/update-the-value-of-an-attribute-in-a-postgresql-jsonb-array-of-objects-across-m/62847982#62847982) with the trailing part converted to [that](https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=54574b75173235fdf9dd1a3120edb3ed) — Barbaros Özhan, Jul 21 '20 at 14:21

Akhilesh Mishra · Answer 1 · 2020-07-21T18:35:27.270

1

As you want to get the path of element first then update it

you can try this:

with cte as (
  select  ('{'||index-1||',name}')::text[] as json_path
  from settings, jsonb_array_elements(strategies) 
  with ordinality arr(strategy,index) where strategy->>'name'='bot'
  )
  
  update settings 
  set strategies = jsonb_set(strategies,cte.json_path,'"bot2"',false) 
  from cte ;

DEMO

edited Jul 21 '20 at 18:35

answered Jul 21 '20 at 11:19

Akhilesh Mishra

5,876
3
16
32

How to do a lookup for array elements in jsonb field?

1 Answers1