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

0
votes

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.

2
sqljsonpostgresqljsonbpostgres-9.6
This is better done outside of SQL. – a_horse_with_no_name
I keep that as my last resort in case there is no way to do it with just one query within the database. – Sam
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. – a_horse_with_no_name
Unfortunately, it's not possible – Sam
is x_id unique for each json element? – Oto Shavadze

2 Answers

0
votes

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
0
votes

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