How to delete a node from a JSONB Array across all table rows in Postges?

Question

I have a table called "Bookmarks" that contains several standard rows and also a JSONB column called "columnsettings"

The content of this JSONB column looks like this.

[
    {
        "data": "id",
        "width": 25
    },
    {
        "data": "field_1",
        "width": 125
    },
    {
        "data": "field_12",
        "width": 125
    },
    {
        "data": "field_11",
        "width": 125
    },
    {
        "data": "field_2",
        "width": 125
    },
    {
        "data": "field_7",
        "width": 125
    },
    {
        "data": "field_8",
        "width": 125
    },
    {
        "data": "field_9",
        "width": 125
    },
    {
        "data": "field_10",
        "width": 125
    }
]

I am trying to write an update statement which would update this columnsettings by removing a specific node I specify. For example, I might want to update the columnsettings and remove just the node where data='field_2' as an example.

I have tried a number of things...I believe it will look something like this, but this is wrong.

update public."Bookmarks"
set columnsettings = 
    jsonb_set(columnsettings, (columnsettings->'data') - 'field_2');

What is the correct syntax to remove a node within a JSONB Array like this?

I did get a version working when there is a single row. This correctly updates the JSONB column and removes the node

UPDATE public."Bookmarks" SET columnsettings = columnsettings - (select position-1 from public."Bookmarks", jsonb_array_elements(columnsettings) with ordinality arr(elem, position) WHERE elem->>'data' = 'field_2')::int

However, I want it to apply to every row in the table. When there is more than 1 row, I get the error " more than one row returned by a subquery used as an expression"

How do I get this query to update all rows in the table?

UPDATED, the answer provided solved my issue.

I now have another JSONB column where I need to do the same filtering. The structure is a bit different, it looks likke this

{
    "filters": [
        {
            "field": "field_8",
            "value": [
                1
            ],
            "header": "Colors",
            "uitype": 7,
            "operator": "searchvalues",
            "textvalues": [
                "Red"
            ],
            "displayfield": "field_8_options"
        }
    ],
    "rowHeight": 1,
    "detailViewWidth": 1059
}

I tried using the syntax the same way as follows:

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
    SELECT bookmarkid, JSONB_AGG(el) as tabsettings
    FROM public."Bookmarks",
         JSONB_ARRAY_ELEMENTS(tabsettings) AS el
    WHERE el->'filters'->>'field' != 'field_8'
    GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid = public."Bookmarks".bookmarkid;

This gives an error: "cannot extract elements from an object"

I thought I had the syntax correct, but how should this line be formatted?

WHERE el->'filters'->>'field' != 'field_8'

I tried this format as well to get to the array. This doesn't given an error, but it doesn't find any matches...even though there are records.

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
    SELECT bookmarkid, JSONB_AGG(el) as tabsettings
    FROM public."Bookmarks",
         JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
    WHERE el->>'field' != 'field_8'
    GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid = public."Bookmarks".bookmarkid;

UPDATED .

This query now seems to work if there is more than one "filter" in the array. However, if there is only 1 element in array which should be excluded, it doesn't remove the item.

UPDATE public."Bookmarks"
SET tabsettings = filtered_elements.tabsettings
FROM (
    SELECT bookmarkid,
           tabsettings || JSONB_BUILD_OBJECT('filters', JSONB_AGG(el)) as tabsettings
    FROM public."Bookmarks",
         -- this must be an array
         JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
    WHERE el->>'field' != 'field_8'
    GROUP BY bookmarkid
) AS filtered_elements
WHERE filtered_elements.bookmarkid =  public."Bookmarks".bookmarkid;

Ruben Helsloot Ruben Helsloot · Accepted Answer · 2020-11-24T20:51:08

You can deconstruct, filter, and re-construct the JSONB array. Something like this should work:

UPDATE bookmarks
SET columnsettings = filtered_elements.columnsettings
FROM (
    SELECT id, JSONB_AGG(el) as columnsettings
    FROM bookmarks,
         JSONB_ARRAY_ELEMENTS(columnsettings) AS el
    WHERE el->>'data' != 'field_2'
    GROUP BY id
) AS filtered_elements
WHERE filtered_elements.id = bookmarks.id;

Using JSONB_ARRAY_ELEMENTS, you transform the JSONB array into rows, one per object, which you call el. Then you can access the data attribute to filter out the "field_2" entry. Finally, you group by id to put the remainign values back together, and update the corresponding row.

EDIT If your data is a nested array in an object, override the object on the specific key:

UPDATE bookmarks
SET tabsettings = filtered_elements.tabsettings
FROM (
    SELECT id,
           tabsettings || JSONB_BUILD_OBJECT('filters', JSONB_AGG(el)) as tabsettings
    FROM bookmarks,
         -- this must be an array
         JSONB_ARRAY_ELEMENTS(tabsettings->'filters') AS el
    WHERE el->>'field' != 'field_2'
    GROUP BY id
) AS filtered_elements
WHERE filtered_elements.id = bookmarks.id;

How to delete a node from a JSONB Array across all table rows in Postges?

1 Answers