Looping JSON Array in JSONB field

Question

I want to loop over JSONB column and get certain values (price, discount_price, and currency) of relevant JSON objects to my filter. But I get this error:

syntax error at or near "FOR"

Value of the parts column which is JSONB:

[
    {
        "item_tags": ["black", "optional"],
        "name": "Keyboard",
        "price": 50,
        "currency": "USD",
        "discount_price": 40
    },
    {
        "item_tags": ["white", "optional"],
        "name": "Mouse",
        "price": 40,
        "currency": "USD",
        "discount_price": 30
    }
]

My query ($1 is the user input. Can be 'optional' or 'required'):

SELECT
    id,
    title,
    FOR element IN SELECT * FROM jsonb_array_elements(parts)
        LOOP
            CASE
                WHEN element->'item_tags' @> $1
                    THEN SELECT element->>'discount_price' AS price, element->>'currency' AS currency
                ELSE SELECT element->>'price' AS price, element->>'currency' AS currency
            END
        END LOOP
FROM items;

This is the output I want to get if $1 is equal to 'optional':

{
    "id": 1,
    "title": "example title",
    "parts": [
        {
            "name": "Keyboard",
            "discount_price": 40,
            "currency": "USD"
        },
        {
            "name": "Mouse",
            "discount_price": 30,
            "currency": "USD"
        }
    ]
}

Any help is highly appreciated. I follow official docs but it is not beginner-friendly. I use PostgreSQL 13.

You can't use a FOR loop inside a SELECT statement. It's unclear to me what you are trying to achieve there. Please [edit] your question and add the expected output based on your sample — , Jul 21 '21 at 20:42
@a_horse_with_no_name Thanks for the comment. Edited and added the changes. Please write if anything further is needed — Ulvi, Jul 21 '21 at 20:56
Your sample data contains two "optional" prices. What is the rule to chose the second over the first? — , Jul 21 '21 at 22:12
@a_horse_with_no_name Sorry, my bad. Both of them should be seen in the output. Edited my question — Ulvi, Jul 22 '21 at 08:50
@a_horse_with_no_name Now I see that I need to use it in WHERE clause because I'm gonna filter data with this user input — Ulvi, Jul 22 '21 at 18:03
@a_horse_with_no_name do you have any suggestions? I was waiting to get answer but nobody posted yet. I can't overcome this problem — Ulvi, Jul 22 '21 at 21:56

score 0 · Answer 1 · answered Jul 23 '21 at 07:05

You need to unnest the array, filter out the unwanted parts, remove the unwanted key, then aggregate the changed parts back into a JSON array.

This can be done using a scalar sub-query:

select id, title, 
       (select jsonb_agg(x.part - 'item_tags') 
        from jsonb_array_elements(i.parts) as x(part)
        where (x.part -> 'item_tags') ? 'optional')
from items i;

The expression x.part - 'item_tags' removes the item_tags key from the JSON object. The ? operator tests if the item_tags array contains the string on the right hand side. And jsonb_agg() then aggregates those JSON values back into an array.

You can pass your parameter in the place of the 'optional' string.

Looping JSON Array in JSONB field

1 Answers1