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 10.22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table reels_data(id int,blocks jsonb);
insert into reels_data values(1,'[{"data":{"imageId":399},"type":"LOGO"},{"data":{"url":"","name":"Bob","email":"","phone":"","jobTitle":"Developer"},"type":"CONTACT_INFO"},{"data":{"playlists":[{"id":"134e3b49-fe08-43b9-b13a-dc886ec0af61","name":"Untitled Playlist","media":[{"id":265,"name":"Test","duration":"104.749","fileUuid":"8a7519b8-92dc-4978-a239-5b25d66caf45","itemType":"TRACK"},{"id":266,"name":"Test 2","duration":"144.163","fileUuid":"7409bbd5-f8a0-46f2-a077-78c14a4dcd80","itemType":"TRACK"},{"id":267,"name":"Test 3","duration":"143.871","fileUuid":"14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5","itemType":"TRACK"}]},{"id":"13e3b49-fe08-43b9-b13a-dc886ec0af61","name":"Untitled Playlist","media":[{"id":265,"name":"Test","duration":"104.749","fileUuid":"8a7519b8-92dc-4978-a239-5b25d66caf45","itemType":"TRACK"},{"id":266,"name":"Test 2","duration":"144.163","fileUuid":"7409bbd5-f8a0-46f2-a077-78c14a4dcd80","itemType":"TRACK"},{"id":267,"name":"Test 3","duration":"143.871","fileUuid":"14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5","itemType":"TRACK"}]}]},"type":"MEDIA"}]');
insert into reels_data values(2,'[{"data": {"imageId": 399}, "type": "LOGO"}, {"data": {"url": "", "name": "Bob", "email": "", "phone": "", "jobTitle": "Developer"}, "type": "CONTACT_INFO"}, {"data": {"playlists": [{"id": "134e3b49-fe08-43b9-b13a-dc886ec0af61", "name": "Untitled Playlist", "media": [{"id": 265, "name": "Test", "duration": "104.749", "fileUuid": "8a7519b8-92dc-4978-a239-5b25d66caf45", "itemType": "TRACK"}, {"id": 266, "name": "Test 2", "duration": "144.163", "fileUuid": "7409bbd5-f8a0-46f2-a077-78c14a4dcd80", "itemType": "TRACK"}, {"id": 265, "name": "Test 3", "duration": "143.871", "fileUuid": "14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5", "itemType": "TRACK"}]}]}, "type": "MEDIA"}]');
1 rows affected
1 rows affected
with cte as(
select
id,
data->'type' "type",
data->'data' "data",
playlists->>'id',
playlists->>'name',
jsonb_build_object('id', playlists->>'id','name', playlists->>'name', 'media',json_agg(z.media) ) "playlists"
from reels_data t1
left join lateral jsonb_array_elements(t1.blocks) x(data) on true
left join lateral jsonb_array_elements(x.data->'data'->'playlists') y(playlists) on true
left join lateral jsonb_array_elements(y.playlists->'media') z(media) on true
where z.media->>'id' is null or z.media->>'id' <>'265'
group by 1,2,3,4,5
), cte1 as
(
select id,jsonb_agg(final) "final_data" from (
select
id,
type,
data,
playlists,
jsonb_build_object('type',type,'data',case when type='"MEDIA"' then jsonb_build_object('playlists',jsonb_agg(playlists)) else data end) "final"
from
cte
group by 1,2,3,4) t1
group by 1
)
update reels_data t1 set blocks= t2."final_data" from cte1 t2 where t1.id=t2.id
2 rows affected
select id,jsonb_pretty(blocks) from reels_data
| id | jsonb_pretty |
|---|---|
| 1 | [ { "data": { "url": "", "name": "Bob", "email": "", "phone": "", "jobTitle": "Developer" }, "type": "CONTACT_INFO" }, { "data": { "imageId": 399 }, "type": "LOGO" }, { "data": { "playlists": [ { "id": "134e3b49-fe08-43b9-b13a-dc886ec0af61", "name": "Untitled Playlist", "media": [ { "id": 266, "name": "Test 2", "duration": "144.163", "fileUuid": "7409bbd5-f8a0-46f2-a077-78c14a4dcd80", "itemType": "TRACK" }, { "id": 267, "name": "Test 3", "duration": "143.871", "fileUuid": "14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5", "itemType": "TRACK" } ] } ] }, "type": "MEDIA" }, { "data": { "playlists": [ { "id": "13e3b49-fe08-43b9-b13a-dc886ec0af61", "name": "Untitled Playlist", "media": [ { "id": 266, "name": "Test 2", "duration": "144.163", "fileUuid": "7409bbd5-f8a0-46f2-a077-78c14a4dcd80", "itemType": "TRACK" }, { "id": 267, "name": "Test 3", "duration": "143.871", "fileUuid": "14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5", "itemType": "TRACK" } ] } ] }, "type": "MEDIA" } ] |
| 2 | [ { "data": { "url": "", "name": "Bob", "email": "", "phone": "", "jobTitle": "Developer" }, "type": "CONTACT_INFO" }, { "data": { "imageId": 399 }, "type": "LOGO" }, { "data": { "playlists": [ { "id": "134e3b49-fe08-43b9-b13a-dc886ec0af61", "name": "Untitled Playlist", "media": [ { "id": 266, "name": "Test 2", "duration": "144.163", "fileUuid": "7409bbd5-f8a0-46f2-a077-78c14a4dcd80", "itemType": "TRACK" } ] } ] }, "type": "MEDIA" } ] |