Remove json object from array of jsons in postgres

Question

I have the following json in my column in posgres database.

Column items in table products

{
    "name": "Super name",
    "type": "Green",
    "information": [
        {
            "name": "first",
            "value": "high"
        },
        {
            "name": "second",
            "value": "medium"
        }
    ],
}

I want to delete json object using jsonb

{
    "name": "second",
    "value": "medium"
}

I try this:

update products set items = jsonb_set(items, '{information}', (items->'information') - '{"name": "second", "value": "medium"}');

I tried different approaches but nothing work correctly.

score 2 · Accepted Answer · 2020-04-07 15:22:10Z

2

The "minus" operator doesn't work on objects, only keys. And it doesn't work on arrays of objects either.

I would write a function that removes a single object from an array.

create function remove_element(p_input jsonb, p_to_remove jsonb)
  returns jsonb
as
$$
  select coalesce(jsonb_agg(t.item order by t.idx), '[]')
  from jsonb_array_elements(p_input) with ordinality as t(item, idx)
  where t.item <> p_to_remove;
$$
language sql 
immutable;

Then you can use it like this:

update products
  set items = jsonb_set(items, '{information}', remove_element(items -> 'information', '{"name": "second", "value": "medium"}')) 
where ...

Online example

edited Apr 7, 2020 at 15:22

answered Apr 7, 2020 at 12:44

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

user330315 Over a year ago

@TheNightmare: works with your sample data: dbfiddle.uk/…

The Nightmare Over a year ago

Yes, when i have two objects in information array, but when i have in array only one item to delete: '{"name": "second", "value": "medium"}' then function set my items to null. Example: dbfiddle.uk/…

user330315 Over a year ago

@TheNightmare: what do you want then? An empty array? Remove the key information completely?

The Nightmare Over a year ago

Empty array will be ok

The Nightmare Over a year ago

Thx. Now looking fine.

Collectives™ on Stack Overflow

Remove json object from array of jsons in postgres

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related