Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 9.6.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table blocks (id int, name varchar, blocks text);

insert into blocks values(
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"}]')
3 rows affected

select 
*  
from blocks cross join lateral jsonb_array_elements(blocks::jsonb) t(x)


id
name
blocks
x


1
Test 1
[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
{"name": "Bob", "type": "INFO", "title": "CFO"}

1
Test 1
[{"name": "Bob", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]
{"type": "FOOTER"}

2
Another
[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]
{"name": "Bob", "type": "INFO", "title": "Manager"}

2
Another
[{"name": "Bob", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]
{"type": "FOOTER"}

3
Final
[{"id": 22, "type": "IMAGE"}]
{"id": 22, "type": "IMAGE"}


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;
3 rows affected

select * from blocks


id
name
blocks


1
Test 1
[{"name": "New Name", "type": "INFO", "title": "CFO"}, {"type": "FOOTER"}]

2
Another
[{"name": "New Name", "type": "INFO", "title": "Manager"}, {"type": "FOOTER"}]

3
Final
[{"id": 22, "type": "IMAGE"}]

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

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