Delete element in a deeply nested array in jsonb column - Postgres

Question

I have a table my_table with a jsonb column that contains some data, for instance, in a single row, the column can contain the following data:

[
  {
    "x_id": "1",
    "type": "t1",
    "parts": [
       { "part_id": "1", price: 400 },
       { "part_id": "2", price: 500 },
       { "part_id": "3", price: 0 }
     ]
  },
  {
    "x_id": "2",
    "type": "t1",
    "parts": [
       { "part_id": "1", price: 1000 },
       { "part_id": "3", price: 60 }
     ]
  },
  {
    "x_id": "3",
    "type": "t2",
    "parts": [
       { "part_id": "1", price: 100 },
       { "part_id": "3", price: 780 },
       { "part_id": "2", price: 990 }
     ]
  }
]

I need help finding how to delete an element from the parts array given a x_id and a part_id.

Example

given x_id=2 and part_id=1, I need the data to be updated to become:

[
  {
    "x_id": "1",
    "type": "t1",
    "parts": [
       { "part_id": "1", price: 400 },
       { "part_id": "2", price: 500 },
       { "part_id": "3", price: 0 }
     ]
  },
  {
    "x_id": "2",
    "type": "t1",
    "parts": [
       { "part_id": "3", price: 60 }
     ]
  },
  {
    "x_id": "3",
    "type": "t2",
    "parts": [
       { "part_id": "1", price: 100 },
       { "part_id": "3", price: 780 },
       { "part_id": "2", price: 990 }
     ]
  }
]

PS1: these data cannot be normalized, so that's not a possible solution.

PS2: I'm running PostgreSQL 9.6

PS3: I have checked this question and this question but my data structure seems too complex compared to the other questions thus I can't apply the given answers.

Edit1: the json data can be big, especially the parts array, which can have from as few as 0 element to thousands.

I keep that as my last resort in case there is no way to do it with just one query within the database. — Sam
– Sam, Commented Apr 23, 2019 at 12:36
Can you change the JSON structure? Arrays are inherently hard to work with. If you can change it to something like this it would be much easier as you can access everything with path like references. — user330315
– user330315, Commented Apr 23, 2019 at 12:45

roman · Accepted Answer · 2019-04-23 16:18:05Z

1

I think you can use #- operator (see functions-json), you just need to find the path to remove the array element from:

select
    data #- p.path
from test as t
    cross join lateral (
        select array[(a.i-1)::text,'parts',(b.i-1)::text]
        from jsonb_array_elements(t.data) with ordinality as a(data,i),
            jsonb_array_elements(a.data->'parts') with ordinality as b(data,i)
        where
            a.data ->> 'x_id' = '2' and
            b.data ->> 'part_id' = '1'
    ) as p(path)

or

update test as t set
    data = data #- (
        select
            array[(a.i-1)::text,'parts',(b.i-1)::text]
        from jsonb_array_elements(t.data) with ordinality as a(data,i),
            jsonb_array_elements(a.data->'parts') with ordinality as b(data,i)
        where
            a.data ->> 'x_id' = '2' and
            b.data ->> 'part_id' = '1'
    )

db<>fiddle demo

update Ok, there's reasonable comment that update part works incorrectly if given path doesn't exist in the data. I guess in this case you're going to either duplicate expression in the where clause:

update test as t set
    data = data #- (
        select
            array[(a.i-1)::text,'parts',(b.i-1)::text]
        from jsonb_array_elements(t.data) with ordinality as a(data,i),
            jsonb_array_elements(a.data->'parts') with ordinality as b(data,i)
        where
            a.data ->> 'x_id' = '2' and
            b.data ->> 'part_id' = '23222'
    )
where
    exists (
        select *
        from jsonb_array_elements(t.data) as a(data),
            jsonb_array_elements(a.data->'parts') as b(data)
        where
            a.data ->> 'x_id' = '2' and
            b.data ->> 'part_id' = '23222'
    )

db<>fiddle demo

or you can use self-join:

update test as t2 set
    data = t.data #- p.path
from test as t
    cross join lateral (
        select array[(a.i-1)::text,'parts',(b.i-1)::text]
        from jsonb_array_elements(t.data) with ordinality as a(data,i),
            jsonb_array_elements(a.data->'parts') with ordinality as b(data,i)
        where
            a.data ->> 'x_id' = '2' and
            b.data ->> 'part_id' = '23232'
    ) as p(path)
where
    t.ctid = t2.ctid

db<>fiddle demo

edited Apr 23, 2019 at 16:18

answered Apr 23, 2019 at 15:04

roman

118k30 gold badges205 silver badges209 bronze badges

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

4 Comments

Sam Over a year ago

Your solution seems to update the data with empty if the given x_id or part_id is/are nonexistent.

Sam Over a year ago

Thanks for the update, however, this doesn't perform well on very large data, where parts array is more than 1000 elements. I run this on such data and it hangs forever.

Sam Over a year ago

The first snippet, as I was providing an existent path and wanted to see the select outcome before updating.

roman Over a year ago

I made a quick test (dbfiddle.uk/…) but honestly I'd not expect this to perform very well on very large amounts of data

Oto Shavadze · Accepted Answer · 2019-04-23 14:23:07Z

0

This should work, just need another unique column (primary key usually)

create test table

create table test_tab(
id serial primary key,
j jsonb
);

insert into test_tab
(j)
values
('[
  {
    "x_id": "1",
    "type": "t1",
    "parts": [
       { "part_id": "1", "price": 400 },
       { "part_id": "2", "price": 500 },
       { "part_id": "3", "price": 0 }
     ]
  },
  {
    "x_id": "2",
    "type": "t1",
    "parts": [
       { "part_id": "1", "price": 1000 },
       { "part_id": "3", "price": 60 }
     ]
  },
  {
    "x_id": "3",
    "type": "t2",
    "parts": [
       { "part_id": "1", "price": 100 },
       { "part_id": "3", "price": 780 },
       { "part_id": "2", "price": 990 }
     ]
  }
]');

Then split json, filter unnecessary data, and recreate json again:

 select id, jsonb_agg( jsonb_build_object('x_id',xid, 'type',type, 'parts', case when inner_arr = '[null]'::jsonb  then parts_arr::jsonb else inner_arr  end) ) 
 from (
    select 
    id, 
     value->>'x_id' as xid, 
    jsonb_agg(inner_arr) as inner_arr,
    max(value->>'parts') as parts_arr,
    max(value->>'type') as type
    from (
        select * , 
        case when value->>'x_id'='2' then jsonb_array_elements(value->'parts')  else NULL end inner_arr 
        from test_tab
        join lateral jsonb_array_elements(j)
        on true
    ) t
    where
    inner_arr->>'part_id'  is distinct from '1'
    group by id, value->>'x_id' 
) t
group by id

answered Apr 23, 2019 at 14:23

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

3 Comments

Sam Over a year ago

I do have a primary key so that's not gonna be a problem, I tried this and it seems to work, I gave some nonexistent ID values and it doesn't crash, so thank you so much. Do you think this solution has any edge cases that might make it crash?

Oto Shavadze Over a year ago

@ElSam - if your json always have same structure as you shown in question, I think it should never crash

Sam Over a year ago

This doesn't perform well on very large data, where parts array is more than 1000 elements. I run this on such data and it hangs forever.

Collectives™ on Stack Overflow

Delete element in a deeply nested array in jsonb column - Postgres

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related