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!
2
Answers
To accomplish this, first recreate the items array using
jsonb_agg
, then replace the jsonb column usingjsonb_set
:Demo here
You just have to query your JSON in the path for suggestions and replace the result into the JSON tree like:
Working sample here