Remove key from multiple nested jsonb objects with unknown keys in PostgreSQL

Question

I have a table with a column of jsonb objects that look similar, but have different top level keys in each row. Each record looks something like this, but with different top level keys:

{
   "10": {
      "key": "value",
      "toDelete": "value"
   },
   "42": {
      "key": "value",
      "toDelete": "value"
   },...
}

I need to do an update to remove the toDelete key/value from each object in each record in every row. It's easy enough to remove each one manually using the #- operator, but there could be hundreds or even thousands of top level keys in each record, so I need some sort of dynamic solution. I tried aggregating all the paths to delete into an array and removing all of them at once using column #- array where the array looks like {{10, toDelete},{42,toDelete}...} but that didn't do the trick.

Answer 1

The structure of the json column is an anti-pattern, I fully agree with the comment by @mvp:

... you should consider extracting your data from json and store it in real SQL table(s) instead.

If you are forced to play with the original data, use the function:

create or replace function remove_nested_object(obj jsonb, key_to_remove text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(key, value- key_to_remove)
    from jsonb_each(obj)
$$;

update my_table
set json_column = remove_nested_object(json_column, 'toDelete')
where json_column::text like '%"toDelete":%';