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.将数组扩展为每个数组元素一行
1.筛选出要删除的元素
1.将所有剩余元素组合到一个新数组中

谢谢S-Man的回复。我能得到我想要的东西。

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 ;

字符串

这是我对 S-Man/a_horse_with_no_name 示例的升级，用于更新room表，其中包含uididentifier和messagejsonb列。这样，如果没有留下任何消息，则插入空[]。

房间

| 信息| messages |
| --| ------------ |
| { 'message_uid'：|'test_id'，文本：'hello' } '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：使用函数的更好版本

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 $$

型