Select jsonb column with a subset of nested array based on id

Question

I have a jsonb column in the following format:

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

I would like to return that same column but only with a subset of the "items" within "suggestions" subject to given item "id".

For example, if I am interested in item with id "foo", the return value should be:

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

I've tried several things so far, including:

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"}]'
  );

and also

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'
  )
);

Unfortunately, none of these worked. I've also tried to figure out how can I solve this with jsonb_path_query_array but the column is way too nested for me to figure out the appropriate query path.

Any help is appreciated!

Answer 1

To accomplish this, first recreate the items array using jsonb_agg, then replace the jsonb column using jsonb_set:

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

Demo here

Answer 2

You just have to query your JSON in the path for suggestions and replace the result into the JSON tree like:

select my_column, 
jsonb_set(my_column, '{suggestions}', sel_item) my_result
from (  select my_column,  
        jsonb_path_query_array(my_column, 
        '$.suggestions[*] ? (@.items.id == "foo")') sel_item
        from my_table 
) as t;

Working sample here