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';

please show us what you tried so far

Prostagma
– Prostagma

2019-07-16 08:30:55 +00:00
Commented Jul 16, 2019 at 8:30 — Prostagma
– Prostagma, Commented Jul 16, 2019 at 8:30

S-Man · Accepted Answer · 2019-07-16 09:29:09Z

4

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;

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

edited Jul 16, 2019 at 9:29

answered Jul 16, 2019 at 9:13

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

If it's important to preserve the original order in the array, you can use with ordinality: dbfiddle.uk/…

hamza sadiqi Over a year ago

Thanks for your response it was really helpfull, i made a slightly modified version of your original code, juste in case of we delete all items in that array we cant add more items to it so i made sure that if its null i replace it with empty array.

hamza sadiqi · Accepted Answer · 2019-07-16 09:54:27Z

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 ;

Ugran · Accepted Answer · 2023-07-30 00:40:46Z

This is my upgrade of the S-Man/a_horse_with_no_name examples for updating room table with uid identifier & message jsonb column. This way if there are no messages left, empty [] is inserted instead.

Room

uid	messages
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11	{ 'message_uid': 'test_id', text: 'hello' }

do $$
declare
    filtered jsonb;
BEGIN

SELECT
    messages,
    jsonb_agg(elems.value order by elems.idx) AS data
FROM
    room,
    jsonb_array_elements(messages) with ordinality as elems(value, idx)
WHERE
    not (elems.value ->> 'text' = 'Hello2') and
    uid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
GROUP BY messages
INTO filtered;

UPDATE room p
SET messages = COALESCE(filtered, '[]'::jsonb)
WHERE uid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
end $$

UPDATE: better version using a function

CREATE OR REPLACE FUNCTION filteredJsonB(messages jsonb, not_key TEXT, not_value TEXT)
   RETURNS jsonb 
  AS
$$
    DECLARE 
        filtered jsonb;
    BEGIN
        SELECT
            jsonb_agg(elems.value ORDER BY elems.idx) AS DATA
        FROM
            jsonb_array_elements(messages) WITH ordinality AS elems(VALUE, idx)
        WHERE
            NOT (elems.value ->> not_key = not_value)
        GROUP BY messages
        INTO filtered;
        RETURN filtered;
    END;
$$ LANGUAGE plpgsql;

do $$
BEGIN

  UPDATE room p
  SET messages = COALESCE(filteredJsonB(p.messages, 'text', 'Hello2'), '[]'::jsonb)
  WHERE uid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
END $$

Collectives™ on Stack Overflow

Remove element from jsonb array with postgres

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related