CODEWITHSUNDEEP
postgresqlsql-updatejsonbpostgresql-10
van_folmert
van_folmert

Reputation: 4507

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

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.

Upvotes: 1

Views: 117

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 1

Related Questions