I have a jsonb object as follows, where I need to add or remove items from the nested array:
{
"GROUP_ONE": [
"FIRST_ITEM",
"SECOND_ITEM"
],
"GROUP_TWO": [
"FIRST_ITEM",
"SECOND_ITEM"
]
}
An update function passes:
- x_id (table)
- x_group (jsonb top level)
- x_item (item to add or remove items from the nested arrays)
- x_is_add (add or remove).
The group may or may not exist.
Is this code optimal or is there a better way to use jsonb functions to achieve this?
update table set list = (
case
when not x_is_add then jsonb_set(list, '{' || x_group || '}', (list->x_group) - x_item)
when x_is_add and list->x_group is null then list || jsonb_build_object(x_group, array[x_item])
when x_is_add and not list->x_group ? x_item then jsonb_set(list, '{' || x_group || '}', list->x_group || jsonb_build_array(x_item))
end)
where id = x_id