Remove element from jsonb array with postgres

Question

I have a table in postgres that contains column of type jsonb, i used to save in this field array of jsons in this format.

post_id                             |questions                                                                                                                                                                                      |
------------------------------------|---------------------------------------
70071d97-06a8-401f-abfc-20ddada4f402|[{"question": "TEST QUESTION", "transaction_id": "ac547b52-72f3-444e-800c-46aaa48855a5"}, {"question":   "TEST QUESTION", "transaction_id": "ac547b52-72f3-444e-800c-46aaa48855ab"}]|

i want to delete an item in that list based on the transaction_id.

post_id                             |questions                                                                                                                                                                                 |
------------------------------------|---------------------------------------
70071d97-06a8-401f-abfc-20ddada4f402|[{"question": "TEST QUESTION", "transaction_id": "ac547b52-72f3-444e-800c-46aaa48855a5"}]|

I have tried couple of methods but None worked, i tried

select questions - '{"question": "TEST QUESTION", "transaction_id": "ac547b52-72f3-444e-800c-46aaa48855a5"}' from posts where post_id = '70071d97-06a8-401f-abfc-20ddada4f402';

Answer 1

step-by-step demo:db<>fiddle

UPDATE posts p
SET questions = data
FROM (
    SELECT
        questions,
        jsonb_agg(elems.value) AS data                       -- 3
    FROM
        posts,
        jsonb_array_elements(questions) elems                -- 1
    WHERE                                                    -- 2
        not (elems.value ->> 'transaction_id' = 'ac547b52-72f3-444e-800c-46aaa48855a5')
    GROUP BY questions
) s
WHERE s.questions = p.questions;

1. Expand array into one row per array element
2. Filter out the element to be deleted
3. Group all remaining element into a new array

Answer 2

Thanks for S-Man response . i was able to acheive what exactly i wanted.

update edz_posts set questions =     
(SELECT
    '[]'::jsonb || jsonb_agg(elems.value)
FROM
    edz_posts,
    jsonb_array_elements(questions) elems 
where post_id = :post_id and universe_id=:universe_id
and not (elems.value ->> 'transaction_id' = :transaction_id)
group by questions)
where post_id = :post_id and universe_id=:universe_id
;
update edz_posts set questions = '[]'::jsonb where questions is null ;