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

Question

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.

Which name you want to update. Name Filed or Name tag in array elements — Akhilesh Mishra
– Akhilesh Mishra, Commented Oct 2, 2020 at 1:46
@AkhileshMishra I want to update the name with a new name, wherever the object has a value type = 'INFO' — ffxsam
– ffxsam, Commented Oct 2, 2020 at 1:57

ffxsam · Accepted Answer · 2020-10-02 19:00:24Z

2

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;

answered Oct 2, 2020 at 19:00

ffxsam

27.9k35 gold badges101 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akhilesh Mishra · Accepted Answer · 2020-10-02 02:40:18Z

1

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

edited Oct 2, 2020 at 2:40

answered Oct 2, 2020 at 2:29

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

1 Comment

ffxsam Over a year ago

Thank you!! The first one is what I needed. I really don't understand it though.. I'll have to study up on Postgres more.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related