How to remove field from each elements json array postgres?-CodePudding

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}';

CodePudding user response：

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.