Remove element from jsonb array

Question

I have the following jsonb. From the array pages I would like to remove the element called 'pageb'. The solutions offered in similar questions are not working for me.

'{
  "data": {
    "id": "a1aldjfg3f",
    "pages": [
      {
        "type": "pagea"
      },
      {
        "type": "pageb"
      }                                
    ],
    "activity": "test"
  }
}'

My script right now looks like this. It doesnt return any error but the elements won't be removed.

  UPDATE database
  SET reports = jsonb_set(reports, '{data,pages}', (reports->'data'->'pages') - ('{"type":"pageb"}'), true)
  WHERE reports->'data'->'pages'  @> '[{"type":"pageb"}]';

Answer 1

The - operator cannot be applied here because the right-hand operand is a string defining a key, per the documentation:

Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.

Removing a json object from a json array can be done by unpacking the array and finding the index of the object. A query using this method may be too complicated, so defining a custom function is very handy in this case.

create or replace function jsonb_remove_array_element(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select arr- (
        select ordinality- 1
        from jsonb_array_elements(arr) with ordinality
        where value = element)::int
$$;

And the update:

update my_table
set reports = 
    jsonb_set(
        reports, 
        '{data,pages}', 
        jsonb_remove_array_element(reports->'data'->'pages', '{"type":"pageb"}')
        )
where reports->'data'->'pages'  @> '[{"type":"pageb"}]';

Working example in rextester.