How to remove field from each elements json array postgres?

Question

I have table

    CREATE TABLE items (
    id BIGINT PRIMARY KEY ,
    data jsonb
);

Format of data

[
    {
        "id": "d20fe90c-137c-4713-bde1-2d4e75178ad3",
        "text": "text",
        "count": 1
    },
    {
        "id": "d20fe90c-137c-4713-bde1-2d4e75178ad4",
        ""text": "text",
        "count": 1
    }
]

How I can remove field count from all elements of data json array?

I try

UPDATE items
SET data = data #- '{count}';

But this query requires index of array element before count as

UPDATE items
    SET data = data #- '{0, count}';

klin · Accepted Answer · 2021-10-07 13:29:51Z

3

There is no operator or built-in function to do that. Unnest the array and aggregate modified elements in the way like this:

update items t
set data = (
    select jsonb_agg(elem- 'count')
    from items
    cross join lateral jsonb_array_elements(data) as arr(elem)
    where id = t.id)

Test it in db<>fiddle.

answered Oct 7, 2021 at 13:29

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to remove field from each elements json array postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related