Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl (id int, js jsonb);
insert into tbl values (1, '{"name": "John smith", "items": {"unknown-key-1": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}, "unknown-key-2": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}]}}}');

CREATE TABLE



INSERT 0 1


with recursive cte(id, obj, arr, path) as (
   -- get all paths to every item in the object, regardless of key name and structure
   select t.id, case when jsonb_typeof(t.js) = 'object' then t.js else '{"null":null}'::jsonb end, 
      case when jsonb_typeof(t.js) = 'array' then t.js else '[null]'::jsonb end, ''
   from tbl t
   union all
   select c.id, case when jsonb_typeof(p_obj.value) = 'object' then p_obj.value 
      when jsonb_typeof(p_arr.value) = 'object' then p_arr.value else 
         (case when jsonb_typeof(p_obj.value) in ('array', 'object') or  
             jsonb_typeof(p_arr.value) in ('array', 'object') then '{"null":null}'::jsonb else '{}'::jsonb end) end,
      case when jsonb_typeof(p_obj.value) = 'array' then p_obj.value 
        when jsonb_typeof(p_arr.value) = 'array' then p_arr.value else 
         (case when jsonb_typeof(p_obj.value) in ('array', 'object') or  
             jsonb_typeof(p_arr.value) in ('array', 'object') then '[null]'::jsonb else '[]'::jsonb end) end,
      c.path || case when c.path != '' then ',' else '' end || case when p_arr.value#>>'{}' is not null then cast(p_arr.r as text) else p_obj.key end
   from cte c cross join jsonb_each(c.obj) p_obj cross join lateral (
      select row_number() over (order by 1) - 1 r, k.value 
      from jsonb_array_elements(c.arr) k) p_arr 
),
paths as (
   -- find the paths generated in the cte above that contain a key 'file-id' with a corresponding value of 'file-1' 
   select c.id, ('{'||c.path||'}')::text[] path from cte c join tbl t on t.id = c.id 
   where ((t.js#>('{'||c.path||'}')::text[]) -> 'file-id')#>>'{}' = 'file-1' 
),
load_update as (
   select row_number() over (partition by p.id) r, p.id, t.js, p.path from paths p join tbl t on p.id = t.id
),
perform_update as (
   -- make the updates in-place
   select u.r, u.id, u.js #- u.path js from load_update u where u.r = 1
   union all
   select u.r, u.id, p.js #- u.path js 
   from perform_update p join load_update u on u.r = p.r + 1 and p.id = u.id
)
update tbl set js = p.js from perform_update p 
where tbl.id = p.id and p.r = (select max(p1.r) from load_update p1 where p1.id = p.id)

UPDATE 1


select jsonb_pretty(js) from tbl;


jsonb_pretty


{
    "name": "John smith",
    "items": {
        "unknown-key-1": {
            "file-array": [
                {
                    "file-id": "file-2"
                },
                {
                    "file-id": "file-3"
                }
            ]
        },
        "unknown-key-2": {
            "file-array": [
                {
                    "file-id": "file-2"
                }
            ]
        }
    }
}



SELECT 1