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:

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

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

CodePudding user response：

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.

CodePudding user response：

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