CODEWITHSUNDEEP
sqlpostgresqljsonb
mike hennessy
mike hennessy

Reputation: 1679

How to remove specific attribute from JSONB array

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

Upvotes: 0

Views: 591

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions