0

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 thejson 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 theQuestsCommon array byQuestType of the parent array andQuestId of the subarray (QuestId == 1, for example).

I found answers likeRemove nested json object from array in postgres andHow to remove object from json array?, but stuck anyway...

askedMay 18, 2023 at 5:01
kozmo's user avatar

1 Answer1

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        )) -- 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))::jsonFROM updated_quests_commonWHERE 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.

answeredMay 18, 2023 at 10:04
Boorsuk's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.