How to remove specific attribute from JSONB array

Question

I have a postgres JSONB column that holds an array like below: I want to run a query which removes the "hide" attribute from any of these objects.

[
    {
        "data": "id",
        "width": 25
    },
    {
        "data": "field_1",
        "width": 125
    },
    {
        "data": "field_3",
        "hide": true,
        "width": 170
    },
    {
        "data": "addedby",
        "width": 178
    },
    {
        "data": "field_67",
        "width": 125,
        "hide": true
    }
]

The desired output after running it would be:

[
    {
        "data": "id",
        "width": 25
    },
    {
        "data": "field_1",
        "width": 125
    },
    {
        "data": "field_3",
        "width": 170
    },
    {
        "data": "addedby",
        "width": 178
    },
    {
        "data": "field_67",
        "width": 125
    }
]

I know to delete a single attribute from a JSONB attribute looks like:

update mytable set columnsettings = columnsettings -hide where tableid=55

But how do I specify to remove this attribute from the entire array? FYI, I'm running postgres 11

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2021-03-09T14:45:12

Just use JSONB_ARRAY_ELEMENTS() function along with subtraction operator in order to derive every element within the array while subtracting hide attribute, and then aggregate seperated elements within an array again such as

SELECT JSONB_PRETTY( JSONB_AGG(jsdata) ) AS result
  FROM ( SELECT JSONB_ARRAY_ELEMENTS( columnsettings ) - 'hide' AS jsdata
           FROM mytable ) AS t

OR within an Update Statement

UPDATE mytable
   SET columnsettings = ( SELECT JSONB_AGG(jsdata)
                           FROM ( SELECT JSONB_ARRAY_ELEMENTS( columnsettings ) 
                                      - 'hide' AS jsdata
                                    FROM mytable ) AS t )

Demo

How to remove specific attribute from JSONB array

1 Answers