英文:
Remove objects from json subarray of array
问题
我有一个表：
create table public.config
(
id text not null,
payload json not null,
updated_at timestamp with time zone default now() not null
);
和json字段具有以下结构：
{
"QuestsCommon":
[
{
"QuestType": "Standard",
"RotationQuestsList": []
},
{
"QuestType": "Daily", 👈️ 需要从子数组 ("RotationQuestsList") 中删除 "QuestType" == "Daily" 的元素
"RotationQuestsList":
[
{
"QuestId": "1", 👈️ 需要删除数组中 "QuestId" == 1 的元素
"GroupId": "Reusable",
"Enabled": false
},
{
"QuestId": "2",
"GroupId": "Reusable",
"Enabled": false
}
]
},
{
"QuestType": "Weekly",
"RotationQuestsList": []
},
{
"QuestType": "Challenge",
"RotationQuestsList":
[
{
"QuestId": "Lucky",
"GroupId": "Regular",
"Enabled": true
}
]
}
]
}
我想编写一个查询，通过父数组的QuestType和子数组的QuestId（例如，QuestId == 1）删除QuestsCommon数组的子数组 (RotationQuestsList) 中的对象。
我发现类似于Remove nested json object from array in postgres和How to remove object from json array?的答案，但仍然卡住了...
英文:
I have a table:
create table public.config
(
id text not null,
payload json not null,
updated_at timestamp with time zone default now() not null
);
and the json field has the following structure:
{
"QuestsCommon":
[
{
"QuestType": "Standard",
"RotationQuestsList": []
},
{
"QuestType": "Daily", 👈️ need to remove elements from subarray ("RotationQuestsList") where "QuestType" == "Daily"
"RotationQuestsList":
[
{
"QuestId": "1", 👈️ need to remove array's element with "QuestId" == 1
"GroupId": "Reusable",
"Enabled": false
},
{
"QuestId": "2",
"GroupId": "Reusable",
"Enabled": false
}
]
},
{
"QuestType": "Weekly",
"RotationQuestsList": []
},
{
"QuestType": "Challenge",
"RotationQuestsList":
[
{
"QuestId": "Lucky",
"GroupId": "Regular",
"Enabled": true
}
]
}
]
}
I'd to write a query that removes object's from the subarray (RotationQuestsList ) of the QuestsCommon array by QuestType of the parent array and QuestId of the subarray (QuestId == 1, for example).
I found answers like Remove nested json object from array in postgres and How to remove object from json array?, but stuck anyway...
答案1
得分: 1
以下是您提供的代码的翻译部分：
WITH updated_quests_common AS (
SELECT id, json_agg(
CASE WHEN element->>'QuestType' != 'Daily' THEN element
ELSE json_build_object('QuestType', 'Daily', 'RotationQuestsList', (
SELECT json_agg(inner_element) FROM json_array_elements((element->>'RotationQuestsList')::json) AS inner_element
WHERE (inner_element->>'QuestId')::int != 1
))
END
) quests_common
FROM config,
json_array_elements((payload->>'QuestsCommon')::json) AS element
GROUP BY id
)
UPDATE config SET payload = (payload::jsonb || jsonb_build_object('QuestsCommon', updated_quests_common.quests_common))::json
FROM updated_quests_common
WHERE config.id = updated_quests_common.id;
英文:
WITH updated_quests_common AS(
SELECT id, json_agg(
CASE WHEN element->>'QuestType' != 'Daily' THEN element
ELSE json_build_object('QuestType', 'Daily', 'RotationQuestsList', (
SELECT json_agg(inner_element) FROM json_array_elements((element->>'RotationQuestsList')::json) AS inner_element
WHERE (inner_element->>'QuestId')::int != 1
)) -- this json_build_object need additional work if you have more keys than you provide
END
) quests_common
FROM config,
json_array_elements((payload->>'QuestsCommon')::json) AS element
GROUP BY id
)
UPDATE config SET payload = (payload::jsonb || jsonb_build_object('QuestsCommon', updated_quests_common.quests_common))::json
FROM updated_quests_common
WHERE config.id = updated_quests_common.id;
Edit: forgot about second condition (need to remove array's element with "QuestId" == 1), hope someone can simplify this.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论