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,jdata jsonb);
insert into test (jdata) values
('{
  "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"
        }
      ]
    }
  }
}')
returning id,jsonb_pretty(jdata);

CREATE TABLE




id
jsonb_pretty


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"
                }
            ]
        }
    }
}



INSERT 0 1


select jsonb_path_query(jdata,'$?(@.**."file-id"<>"file-1")') from test;


jsonb_path_query


{"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"}]}}}



SELECT 1


select *
from test
    ,jsonb_each(jdata->'items')a(item,contents)
    ,jsonb_array_elements(contents->'file-array')with ordinality b(file_obj,file_index);    


id
jdata
item
contents
file_obj
file_index


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"}]}}}
unknown-key-1
{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}
{"file-id": "file-1"}
1

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"}]}}}
unknown-key-1
{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}
{"file-id": "file-2"}
2

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"}]}}}
unknown-key-1
{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}
{"file-id": "file-3"}
3

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"}]}}}
unknown-key-2
{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}]}
{"file-id": "file-1"}
1

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"}]}}}
unknown-key-2
{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}]}
{"file-id": "file-2"}
2



SELECT 5


select jsonb_pretty(jdata),
  item,
  file_index-1 as file_index,
  jsonb_pretty(jdata#-array['items',item,'file-array',file_index-1]::text[])
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';


jsonb_pretty
item
file_index
jsonb_pretty


{
    "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"
                }
            ]
        }
    }
}
unknown-key-1
0
{
    "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-1"
                },
                {
                    "file-id": "file-2"
                }
            ]
        }
    }
}

{
    "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"
                }
            ]
        }
    }
}
unknown-key-2
0
{
    "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-2"
                }
            ]
        }
    }
}



SELECT 2


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
jdata


1
{"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"}]}}}



UPDATE 1

id	jdata	item	contents	file_obj	file_index
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"}]}}}	unknown-key-1	{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}	{"file-id": "file-1"}	1
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"}]}}}	unknown-key-1	{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}	{"file-id": "file-2"}	2
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"}]}}}	unknown-key-1	{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}, {"file-id": "file-3"}]}	{"file-id": "file-3"}	3
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"}]}}}	unknown-key-2	{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}]}	{"file-id": "file-1"}	1
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"}]}}}	unknown-key-2	{"file-array": [{"file-id": "file-1"}, {"file-id": "file-2"}]}	{"file-id": "file-2"}	2

jsonb_pretty	item	file_index	jsonb_pretty
{ "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" } ] } } }	unknown-key-1	0	{ "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-1" }, { "file-id": "file-2" } ] } } }
{ "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" } ] } } }	unknown-key-2	0	{ "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-2" } ] } } }