如何用PostgreSQL命令从JSON数组删除用户并恢复至主用户数组

核心思路 #

要实现从Deletados表的usuariosDeletados JSON数组中移除指定用户并恢复到主用户表，需完成三步操作：提取目标用户、插入主表、更新删除数组。以下是基于JSONB（比JSON更适合操作）的具体实现：

前提假设 #

• 主用户表名为Usuarios，包含id（主键）、nome、email等字段，与删除数组内的用户结构一致
• Deletados表的usuariosDeletados字段为JSONB类型（若原字段是JSON，可在代码中用::JSONB转换）

完整函数代码 #

CREATE OR REPLACE FUNCTION recuperaUsuario(p_usuario_id INT)
RETURNS VOID AS $$
DECLARE
    v_usuario JSONB;
BEGIN
    -- 1. 从删除数组中匹配目标用户
    SELECT elem
    INTO v_usuario
    FROM Deletados, jsonb_array_elements(usuariosDeletados) elem
    WHERE (elem->>'id')::INT = p_usuario_id;

    -- 无匹配用户时抛出异常
    IF v_usuario IS NULL THEN
        RAISE EXCEPTION 'Usuário ID % não existe na lista de deletados', p_usuario_id;
    END IF;

    -- 2. 将用户恢复到主表（处理主键冲突，可选覆盖或忽略）
    INSERT INTO Usuarios (id, nome, email)
    VALUES (
        (v_usuario->>'id')::INT,
        v_usuario->>'nome',
        v_usuario->>'email'
    )
    ON CONFLICT (id) DO UPDATE SET
        nome = EXCLUDED.nome,
        email = EXCLUDED.email; -- 若无需覆盖，改为DO NOTHING

    -- 3. 从删除数组中移除目标用户
    UPDATE Deletados
    SET usuariosDeletados = (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(usuariosDeletados) elem
        WHERE (elem->>'id')::INT != p_usuario_id
    )
    WHERE EXISTS (
        SELECT 1
        FROM jsonb_array_elements(usuariosDeletados) elem
        WHERE (elem->>'id')::INT = p_usuario_id
    );
END;
$$ LANGUAGE plpgsql;

关键细节说明 #

1. JSON数组处理：

   • 用jsonb_array_elements拆解数组，逐个匹配用户ID
   • 用jsonb_agg重新聚合过滤后的元素，生成移除目标用户后的新数组

2. 冲突处理：

   • 插入主表时用ON CONFLICT避免主键重复报错，可根据需求选择覆盖现有数据或忽略

3. 异常判断：

   • 先检查目标用户是否存在，避免无效操作并给出明确报错信息

测试示例 #

1. 插入测试数据到Deletados表：

INSERT INTO Deletados (usuariosDeletados)
VALUES ('[{"id": 1, "nome": "João Silva", "email": "joao@exemplo.com"}, {"id": 2, "nome": "Maria Souza", "email": "maria@exemplo.com"}]');

2. 调用恢复函数：

SELECT recuperaUsuario(1);

3. 验证结果：

• 查看Usuarios表是否新增ID=1的用户
• 查看Deletados表的usuariosDeletados数组是否仅保留ID=2的用户

内容的提问来源于stack exchange，提问作者Hyper