Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test(
  id int generated by default as identity primary key,
  description text,
  jdata jsonb);
insert into test (description,jdata) values
('base',
'{"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" }
      ]
    }
  }
}'),
('additional file-array element keys',
'{"name": "John smith",
  "items": {
    "unknown-key-1": {
      "file-array": [
        { "file-id": "file-1" },
        { "file-id": "file-2" },
        { "file-id": "file-3",
          "u1f3key":"u1f3key" }
      ]
    },
    "unknown-key-2": {
      "file-array": [
        { "file-id": "file-1" },
        { "file-id": "file-2",
          "u2f2key":"u2f2val" }
      ]
    }
  }
}'),
('non-object unknown-keys',
'{"name": "John smith",
  "items": {
    "unknown-key-0": true,
    "unknown-key-1": {
      "file-array": [
        { "file-id": "file-1" },
        { "file-id": "file-2" },
        { "file-id": "file-3"}
      ]
    },
    "unknown-key-3": {"arr":[1,2,3]},
    "unknown-key-2": {
      "file-array": [
        { "file-id": "file-1" },
        { "file-id": "file-2" }
      ]
    }
  }
}'),
('empty items',
'{"name": "John smith",
  "items": {}
}'),
('mix',
'{"name": "John smith",
  "items": {"something-else": 1,
    "unknown-key-0": true,
    "unknown-key-1": {
      "file-array": [
        { "file-id": "file-1" },
        { "file-id": "file-2" },
        { "file-id": "file-3",
          "u1f3key":"u1f3key" }
      ]
    },
    "unknown-key-3": {"arr":[1,2,3]},
    "unknown-key-2": {
      "file-array": []
    }
  }
}')
returning id,description,jdata;

CREATE TABLE

id	description	jdata
1	base	{"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"}]}}}
2	additional file-array element keys	{"name": "John smith", "items": {"unknown-key-1": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2", "u2f2key": "u2f2val"}]}}}
3	non-object unknown-keys	{"name": "John smith", "items": {"unknown-key-0": true, "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"}]}, "unknown-key-3": {"arr": [1, 2, 3]}}}
4	empty items	{"name": "John smith", "items": {}}
5	mix	{"name": "John smith", "items": {"unknown-key-0": true, "unknown-key-1": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": []}, "unknown-key-3": {"arr": [1, 2, 3]}, "something-else": 1}}

INSERT 0 5

table test;

with recursive required_changes as (
    select id,
      jdata,
      item,
      file_index-1 as file_index,
      row_number()over(partition by id) as update_round
    from test
        ,jsonb_each(jdata->'items')a(item,contents)
        ,jsonb_array_elements(contents->'file-array')
         with ordinality b(file_obj,file_index)
    where (file_obj->>'file-id')='file-1')
,iteratively_merged_changes as (
    select id,
         jdata#-array['items',item,'file-array',file_index]::text[] jdata,
         2 as next_round
    from required_changes where update_round=1
    union
    select a.id,
         a.jdata#-array['items',item,'file-array',file_index]::text[],
         a.next_round+1
    from iteratively_merged_changes a join required_changes b 
    on a.id=b.id 
    and b.update_round=a.next_round)
,final_batch as (
    select distinct on(id)id,jdata 
    from iteratively_merged_changes order by id,next_round desc)
update test t set jdata=f.jdata
from final_batch f where t.id=f.id
returning t.*;

id	description	jdata
1	base	{"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"}]}}}
2	additional file-array element keys	{"name": "John smith", "items": {"unknown-key-1": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2", "u2f2key": "u2f2val"}]}}}
3	non-object unknown-keys	{"name": "John smith", "items": {"unknown-key-0": true, "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"}]}, "unknown-key-3": {"arr": [1, 2, 3]}}}
4	empty items	{"name": "John smith", "items": {}}
5	mix	{"name": "John smith", "items": {"unknown-key-0": true, "unknown-key-1": {"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": []}, "unknown-key-3": {"arr": [1, 2, 3]}, "something-else": 1}}

SELECT 5

id	description	jdata
1	base	{"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"}]}}}
2	additional file-array element keys	{"name": "John smith", "items": {"unknown-key-1": {"file-array": [{"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": [{"file-id": "file-2", "u2f2key": "u2f2val"}]}}}
3	non-object unknown-keys	{"name": "John smith", "items": {"unknown-key-0": true, "unknown-key-1": {"file-array": [{"file-id": "file-2"}, {"file-id": "file-3"}]}, "unknown-key-2": {"file-array": [{"file-id": "file-2"}]}, "unknown-key-3": {"arr": [1, 2, 3]}}}
5	mix	{"name": "John smith", "items": {"unknown-key-0": true, "unknown-key-1": {"file-array": [{"file-id": "file-2"}, {"file-id": "file-3", "u1f3key": "u1f3key"}]}, "unknown-key-2": {"file-array": []}, "unknown-key-3": {"arr": [1, 2, 3]}, "something-else": 1}}

UPDATE 4