我有一个jsonb列的格式如下：

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

我想返回相同的列，但只包含"suggestions"中"items"的子集，该子集受给定项"id"的约束。
例如，如果我对id为"foo"的item感兴趣，返回值应该是：

{
  "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 *
FROM my_table
WHERE jsonb_array_length(my_column->'suggestions') > 0
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(my_column->'suggestions') AS suggestions(items)
    WHERE items->'items' @> '[{"id": "foo"}]'
  );

而且

SELECT *
FROM my_table
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(my_column->'suggestions') AS suggestions(suggestion)
  WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(suggestion->'items') AS items
    WHERE items->>'id' = 'foo'
  )
);

不幸的是，这些都没有工作。我还试图用jsonb_path_query_array来解决这个问题，但是这个列太嵌套了，我无法找到合适的查询路径。
任何帮助是赞赏！