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

Akhilesh Mishra Akhilesh Mishra · Accepted Answer · 2020-11-17T09:37:37

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

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

1 Answers