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..
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.
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
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
[{"task_customs": ["a", "c"], "another_key": "another_value"}] it will update to [{"task_customs": ["a", "c"]}]..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
task_customsin the outer array?[{"task_customs": ["a", "c"], "another_key": "another_value"}]..