how to delete data array on jsonb postgresql

Question

how to update array data in jsonb column on database postgresql?

for example on table table1 i have column attribute that have value like this:

id	attribute
1	[{"task_customs": ["a", "b", "c"]}]
2	[{"task_customs": ["d", "e", "f"]}]

for example if i want to delete b from id 1, so it will be like this on attribute column

id	attribute
1	[{"task_customs": ["a", "c"]}]
2	[{"task_customs": ["d", "e", "f"]}]

already do some research but didn't get what i need..

Will the outer array always contain exactly one element? What if you have multiple elements with the key `task_customs` in the outer array? — , Mar 08 '22 at 10:47
@a_horse_with_no_name there's no possibility to have duplicate key, but the data may can have more than one key for example `[{"task_customs": ["a", "c"], "another_key": "another_value"}]`.. — Jazuly, Mar 08 '22 at 16:25
Hi Jazuly, I've edited the answer depending on the last need on your comment — Barbaros Özhan, Mar 09 '22 at 08:05

score 2 · Answer 1 · answered Mar 08 '22 at 13:00

2

try this :

(a) Delete 'b' acccording to its position in the array :

UPDATE table1
   SET attribute = attribute #- array['0', 'task_customs', '1'] :: text[]
 WHERE id = 1

(b) Delete 'b' without knowing its position in the array :

WITH list AS
( SELECT id, to_jsonb(array[jsonb_build_object('task_customs', jsonb_agg(i.item ORDER BY item_id))]) AS new_attribute
    FROM table1
   CROSS JOIN LATERAL jsonb_array_elements_text(attribute#>'{0,task_customs}') WITH ORDINALITY AS i(item,item_id)
   WHERE id = 1
     AND i.item <> 'b'
  GROUP BY id
)
UPDATE table1 AS t
   SET attribute = l.new_attribute
  FROM list AS l
 WHERE t.id = l.id

see the test result in dbfiddle.

answered Mar 08 '22 at 13:00

Edouard

6,577
1
9
20

its not working when i have more then one object, for example [{"task_customs": ["a", "c"], "another_key": "another_value"}] it will update to [{"task_customs": ["a", "c"]}].. – Jazuly Mar 09 '22 at 05:56
There is no magic function for deleting part of a json data in pgsql, so you have to break down and then rebuild your json data, and this solution highly depends on your json data struture. My answer is based on your initial question and set of example. – Edouard Mar 09 '22 at 07:54
yup, thanks for the answer, i solved this based on your and make some modification.. – Jazuly Mar 09 '22 at 07:58

Barbaros Özhan · Answer 2 · 2022-03-09T08:03:32.433

One option is to start splitting the JSONB value by using jsonb_to_recordset such as

UPDATE table1 AS t
   SET attribute = 
      (
        SELECT json_build_array(
                            jsonb_build_object('task_customs',task_customs::JSONB - 'b')
                          ) 
          FROM table1,
       LATERAL jsonb_to_recordset(attribute) AS (task_customs TEXT)
         WHERE id = t.id
      ) 
 WHERE id = 1

Demo

Edit : If you need more elements as expressed within the comment then you can rather prefer using

UPDATE table1 AS t
   SET attribute = 
      (
        SELECT jsonb_agg(
                    jsonb_build_object(key,je.value::JSONB - 'b')
               ) 
          FROM table1,
       LATERAL jsonb_array_elements_text(attribute) AS atr,
       LATERAL jsonb_each_text(atr::JSONB) AS je
         WHERE id = t.id
      ) 
 WHERE id = 1

Demo

its not working when i have more then one object, for example `[{"task_customs": ["a", "c"], "another_key": "another_value"}]` it will update to `[{"task_customs": ["a", "c"]}]`.. — Jazuly, Mar 09 '22 at 05:50

score 0 · Answer 3 · answered Mar 09 '22 at 07:50

i solve this issue by combining both answer from Edouard and Barbaros

this is my final query

UPDATE table1 AS t
   SET attribute = 
      jsonb_set(
        attribute,
        '{0,task_customs}',
        (
          SELECT task_customs::JSONB - 'b'
          FROM table1
          CROSS JOIN LATERAL jsonb_to_recordset(attribute) AS (task_customs TEXT)
          WHERE id = t.id
        ) 
      )
WHERE id = 1

see the test result in dbfiddle

how to delete data array on jsonb postgresql

3 Answers3