CODEWITHSUNDEEP
sqljsonpostgresql
ffxsam
ffxsam

Reputation: 27793

How to update objects in a JSON array based on a specific key/value pair in Postgres

I have data that looks like this:

id  name    blocks
1   Test 1  [{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
2   Another [{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]
3   Final   [{"id": 22, "type": "IMAGE"}]

The user may want to change their name across all rows, so I'd need to have a Postgres JSON version of:

UPDATE blocks SET name = 'New Name' WHERE type = 'INFO';

How do I write a query that will search these arrays of objects for a specific type key of value "INFO"? The big catch here is that these objects can be in any order in the array. So the INFO block isn't always necessarily at index 0.

Upvotes: 1

Views: 691

Answers (2)

ffxsam
ffxsam

Reputation: 27793

This is ultimately what I went with (based on the accepted answer), just cleaned up a bit and named using the real table names & JSONB structure. This also makes sure a user can only mass-update their own data, not someone else's.

WITH new_data AS (
  SELECT id, json_agg(
    CASE
      WHEN blocks.block->>'type' = 'CONTACT_INFO'
        THEN jsonb_set(blocks.block, '{data, name}', '"New Name"', false)
      ELSE
        blocks.block
    END) AS new_block
  FROM reels
  CROSS JOIN LATERAL jsonb_array_elements(blocks) blocks(block)
  WHERE owner_id = '9292dcc6-f906-418a-aee0-074b297bfb52'
  GROUP BY id
)
UPDATE reels
SET blocks = new_data.new_block
FROM new_data
WHERE reels.id = new_data.id;

Upvotes: 2

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

There are 2 name fields in your example.

If you want to update the name tag in the json field with your condition then try below query:

with cte as 
(
select 
id,
name,
json_agg(case when t.x->>'type'='INFO' then jsonb_set(t.x,'{name}','"New Name"',false) else t.x end) "new_value"
from blocks cross join lateral jsonb_array_elements(blocks::jsonb) t(x)
group by 1,2
)
update blocks t1 
set blocks=t2.new_value
from cte t2 
where t1.id=t2.id;

DEMO

If you want to update the name field of your table with your condition then try below query:

with cte as 
(
select 
id,
name,
bool_or(t.x->>'type'='INFO') "new_value"
from blocks cross join lateral jsonb_array_elements(blocks::jsonb) t(x)
group by 1,2
)
update blocks t1 
set name='New Name'
from cte t2 
where t1.id=t2.id and t2.new_value=true;

DEMO

Upvotes: 1

Related Questions