json - 如何从 Postgres JSONB 数组中深度删除对象?
问题描述
我正在使用 Postgres 10.12,并且我有一个表 ( reels_data
),其中包含一个名为 的 jsonb 列blocks
,它是一个对象数组,每个对象都有自己的类型和数据对象。例子:
[
{
"type" : "LOGO",
"data" : {
"imageId" : 399
}
},
{
"type" : "CONTACT_INFO",
"data" : {
"email" : "",
"phone" : "",
"url" : "",
"name" : "Bob",
"jobTitle" : "Developer"
}
},
{
"type" : "MEDIA",
"data" : {
"playlists" : [
{
"id" : "134e3b49-fe08-43b9-b13a-dc886ec0af61",
"name" : "Untitled Playlist",
"media" : [
{
"id" : 265,
"fileUuid" : "8a7519b8-92dc-4978-a239-5b25d66caf45",
"itemType" : "TRACK",
"name" : "Test",
"duration" : "104.749"
},
{
"id" : 266,
"fileUuid" : "7409bbd5-f8a0-46f2-a077-78c14a4dcd80",
"itemType" : "TRACK",
"name" : "Test 2",
"duration" : "144.163"
},
{
"id" : 267,
"fileUuid" : "14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5",
"itemType" : "TRACK",
"name" : "Test 3",
"duration" : "143.871"
}
]
}
]
}
}
]
我的挑战是,如果用户删除 ID 为 265 的媒体,则必须从数组中的所有播放列表中将其从所有blocks
type中提取"MEDIA"
,并使其更复杂。playlists
这些块可以按任何顺序排列,所以我不能假设索引为 2。可能有一个播放列表或 10 个,要删除的媒体可能不存在或存在于这些播放列表中的几个。
我可以编写一个 Postgres 查询来删除所有 ID 媒体x
吗?还是将其编写为 SQL 查询更好,以简单地检索上述数据,在 JavaScript 中添加一些数据处理,然后构建并提交 SQL 事务以使用新数据更新几行?效率是重中之重(不对数据库服务器征税)。
解决方案
考虑到您的结构是固定的,请尝试以下查询:
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
它将替换所有具有给定 ID 的对象。