How to deeply remove an object from a Postgres JSONB array?

Question

I'm using Postgres 10.12, and I have a table (reels_data) that has a jsonb column called blocks, which is an array of objects, each with its own type and data object. Example:

[
  {
    "type" : "LOGO",
    "data" : {
      "imageId" : 399
    }
  },
  {
    "type" : "CONTACT_INFO",
    "data" : {
      "email" : "",
      "phone" : "",
      "url" : "",
      "name" : "Bob",
      "jobTitle" : "Developer"
    }
  },
  {
    "type" : "MEDIA",
    "data" : {
      "playlists" : [
        {
          "id" : "134e3b49-fe08-43b9-b13a-dc886ec0af61",
          "name" : "Untitled Playlist",
          "media" : [
            {
              "id" : 265,
              "fileUuid" : "8a7519b8-92dc-4978-a239-5b25d66caf45",
              "itemType" : "TRACK",
              "name" : "Test",
              "duration" : "104.749"
            },
            {
              "id" : 266,
              "fileUuid" : "7409bbd5-f8a0-46f2-a077-78c14a4dcd80",
              "itemType" : "TRACK",
              "name" : "Test 2",
              "duration" : "144.163"
            },
            {
              "id" : 267,
              "fileUuid" : "14c0d325-bfce-4ac5-a4f6-3edaa0e86ac5",
              "itemType" : "TRACK",
              "name" : "Test 3",
              "duration" : "143.871"
            }
          ]
        }
      ]
    }
  }
]

My challenge is, if a user deletes media with ID 265, it has to be pulled from all the blocks of type "MEDIA", and to make it more complicated, from all of the playlists in the playlists array.

These blocks can be in any order, so I can't assume an index of 2. And there could be one playlist or 10, and the media to remove could exist in none or several of these playlists.

Is there a single Postgres query I could write to remove all media of ID x? Or is this better written as a SQL query to simply retrieve the above data, add some data processing in JavaScript, and then a build & commit a SQL transaction to update several rows with new data? Efficiency is the top priority (not taxing the DB server).

@AkhileshMishra Version 10.12. I updated my post to reflect this. — ffxsam
– ffxsam, Commented Nov 17, 2020 at 3:25

Akhilesh Mishra · Accepted Answer · 2020-11-17 09:37:37Z

0

Considering your structure is fixed, please try below query:

with cte as(
select 
id,
data->'type' "type",
data->'data' "data",
playlists->>'id', 
playlists->>'name',
jsonb_build_object('id', playlists->>'id','name', playlists->>'name', 'media',json_agg(z.media) ) "playlists"
from reels_data  t1
left join lateral jsonb_array_elements(t1.blocks) x(data) on true
 left join lateral jsonb_array_elements(x.data->'data'->'playlists') y(playlists) on true
left join lateral jsonb_array_elements(y.playlists->'media') z(media) on true
where  z.media->>'id' is null or z.media->>'id' <>'265' 
group by 1,2,3,4,5
), 
cte1 as
(
select id,jsonb_agg(final) "final_data" from (
select 
id,
type, 
data,
playlists,
jsonb_build_object('type',type,'data',case when type='"MEDIA"' then jsonb_build_object('playlists',jsonb_agg(playlists)) else data end) "final"
from 
cte
group by 1,2,3,4) t1
group by 1
)

update reels_data t1 set blocks= t2."final_data" from cte1 t2 where t1.id=t2.id

It will replace all the objects with given id.

DEMO

answered Nov 17, 2020 at 9:37

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

3 Comments

ffxsam Over a year ago

Thank you! This is really close, it almost works, except when I have two playlist objects in the playlists array, one of them gets removed entirely.

ffxsam Over a year ago

Another related comment: the one down-side of this approach is that if the playlist JSON schema suddenly has new properties in it, this SQL query must be updated each time so it builds the proper jsonb field. It's too bad Postgres doesn't have a way to just build an object using all the existing properties.

Akhilesh Mishra Over a year ago

If you want complete dynamic solution then it can be done through recursive function and its call but for that there should be a sample json with all possible scenario.

Collectives™ on Stack Overflow

How to deeply remove an object from a Postgres JSONB array?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related