Remove objects from json subarray of array

Question

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...

Boorsuk · Accepted Answer · 2023-05-18 11:05:26Z

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.

Collectives™ on Stack Overflow

Remove objects from json subarray of array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related