1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.