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

Possible duplicate of Remove jsonb array element by value

Spasa Mihajlovic
– Spasa Mihajlovic

2019-03-05 10:39:32 +00:00
Commented Mar 5, 2019 at 10:39 — Spasa Mihajlovic
– Spasa Mihajlovic, Commented Mar 5, 2019 at 10:39
The solution in that question just doesn't work to me.

Battalgazi
– Battalgazi

2019-03-05 10:48:04 +00:00
Commented Mar 5, 2019 at 10:48 — Battalgazi
– Battalgazi, Commented Mar 5, 2019 at 10:48

klin · Accepted Answer · 2019-03-05 22:36:06Z

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.

Ancoron · Accepted Answer · 2019-03-05 22:20:11Z

The following is a combination of the answer provided for deleting an element inside an array reliably and the PostgreSQL's ability to use data-modifying WITH statements, but it needs an identity column (id in my test table) to work because of necessary correlation:

WITH new_reports AS (
    SELECT
        id,
        reports #- array['data','pages',(position - 1)::text] AS new_value
    FROM
        test,
        jsonb_array_elements(reports->'data'->'pages') WITH ORDINALITY arr(item, position)
    WHERE
        test.reports->'data'->'pages' @> '[{"type":"pageb"}]'
        AND
        item->>'type' = 'pageb'
    )
UPDATE test SET reports = new_reports.new_value FROM new_reports WHERE test.id = new_reports.id;

The test data I used:

SELECT reports FROM test;
                                               reports                                               
-----------------------------------------------------------------------------------------------------
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pagec"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pageb"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pageb"}, {"type": "pagec"}], "activity": "test"}}
(3 rows)

...and after executing the query:

SELECT reports FROM test;
                                               reports                                               
-----------------------------------------------------------------------------------------------------
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}, {"type": "pagec"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagea"}], "activity": "test"}}
 {"data": {"id": "a1aldjfg3f", "pages": [{"type": "pagec"}], "activity": "test"}}
(3 rows)

I hope that works for you.

MartenCatcher · Accepted Answer · 2020-02-13 09:39:19Z

0

There you go

do $$
declare newvar jsonb;
begin
    newvar := jsonb '{ "customer": "John Doe", "buy": [{"product": "Beer","qty": 6},{"product": "coca","qty": 5}]}';
    newvar := jsonb_set(newvar,'{buy}', jsonb_remove((newvar->>'buy')::jsonb,'{"product": "Beer"}'));
    newvar := jsonb_set(newvar,'{buy}', jsonb_add((newvar->>'buy')::jsonb,'{"product": "cofe","qty": 6}'));
    RAISE NOTICE '%', newvar; 
end $$

create or replace function jsonb_remove(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select ('['||coalesce(string_agg(r::text,','),'')||']')::jsonb from jsonb_array_elements(arr) r where r @> element=false
$$;

create or replace function jsonb_add(arr jsonb, element jsonb)
returns jsonb language sql immutable as $$
    select arr||element
$$;

edited Feb 13, 2020 at 9:39

MartenCatcher

2,8958 gold badges29 silver badges41 bronze badges

answered Feb 13, 2020 at 9:13

Hung Tran DI

91 bronze badge

Comments

Denis N · Accepted Answer · 2023-03-24 07:50:24Z

0

Enhanced variation of klin`s answer that supports the absence of a search element in the array

CREATE OR REPLACE FUNCTION jsonb_remove_array_element(arr jsonb, element jsonb) 
RETURNS jsonb LANGUAGE plpgsql IMMUTABLE AS $$
  DECLARE _idx integer;
  DECLARE _result jsonb;
  BEGIN
    _idx := (SELECT ordinality - 1 FROM jsonb_array_elements(arr) WITH ordinality WHERE value = element);
    IF _idx IS NOT NULL 
    THEN 
      _result := arr - _idx;
    ELSE
      _result := arr;
    END IF;
    RETURN _result;
  END;
$$;

answered Mar 24, 2023 at 7:50

Denis N

1

Collectives™ on Stack Overflow

Remove element from jsonb array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related