Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (
  my_column jsonb
);

insert into my_table values
('{
  "targets": {
    "01d7de90-57fd-4c4f-b7c9-3b956762fe25": {
      "id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25",
      "name": "target1"
    },
    "0f43e1fe-132e-464b-8284-4a9947a70c1c": {
      "id": "0f43e1fe-132e-464b-8284-4a9947a70c1c",
      "name": "target2"
    }
  },
  "suggestions": [
    {
      "items": [
        {
          "id": "foo",
          "code": "item1"
        }
      ],
      "groupId": 1
    },
    {
      "items": [
        {
          "id": "bar",
          "code": "item2"
        }
      ],
      "groupId": 2
    }
  ]
}');

CREATE TABLE



INSERT 0 1


select my_column, jsonb_set(my_column::jsonb, '{suggestions}', items_updated::jsonb) edited
from (
  select my_column, jsonb_agg(items) as items_updated
  from my_table,
     jsonb_array_elements(my_column->'suggestions') as items
  where value->'items' @> '[{"id": "foo"}]'
  group by my_column
) as s


my_column
edited


{"targets": {"01d7de90-57fd-4c4f-b7c9-3b956762fe25": {"id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25", "name": "target1"}, "0f43e1fe-132e-464b-8284-4a9947a70c1c": {"id": "0f43e1fe-132e-464b-8284-4a9947a70c1c", "name": "target2"}}, "suggestions": [{"items": [{"id": "foo", "code": "item1"}], "groupId": 1}, {"items": [{"id": "bar", "code": "item2"}], "groupId": 2}]}
{"targets": {"01d7de90-57fd-4c4f-b7c9-3b956762fe25": {"id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25", "name": "target1"}, "0f43e1fe-132e-464b-8284-4a9947a70c1c": {"id": "0f43e1fe-132e-464b-8284-4a9947a70c1c", "name": "target2"}}, "suggestions": [{"items": [{"id": "foo", "code": "item1"}], "groupId": 1}]}



SELECT 1


select jsonb_pretty(jsonb_set(my_column::jsonb, '{suggestions}', items_updated::jsonb)) edited
from (
  select my_column, jsonb_agg(items) as items_updated
  from my_table,
     jsonb_array_elements(my_column->'suggestions') as items
  where value->'items' @> '[{"id": "foo"}]'
  group by my_column
) as s


edited


{
    "targets": {
        "01d7de90-57fd-4c4f-b7c9-3b956762fe25": {
            "id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25",
            "name": "target1"
        },
        "0f43e1fe-132e-464b-8284-4a9947a70c1c": {
            "id": "0f43e1fe-132e-464b-8284-4a9947a70c1c",
            "name": "target2"
        }
    },
    "suggestions": [
        {
            "items": [
                {
                    "id": "foo",
                    "code": "item1"
                }
            ],
            "groupId": 1
        }
    ]
}



SELECT 1