如何使用Sequelize向PostgreSQL的JSONB列数组推送数据

问题原因 #

你原来的写法直接将expenseEditHistory赋值为普通对象，Sequelize会把其中的sequelize.fn('array_append', ...)序列化为字符串，而非执行对应的SQL函数，导致无法正确追加JSONB数组元素。PostgreSQL的JSONB类型需要使用专门的JSON函数来修改内部结构。

解决方案 #

需要利用PostgreSQL的jsonb_set函数来同时更新last_updated_by字段和追加history数组元素，结合Sequelize的literal或fn方法实现安全的批量更新。

方法一：使用sequelize.literal（推荐，更清晰且防SQL注入） #

await Expenses.update(
    {
        isDeleted: true,
        expenseEditHistory: sequelize.literal(`
            jsonb_set(
                // 先更新last_updated_by字段
                jsonb_set(
                    // 处理字段为空的情况，初始化默认结构
                    coalesce(expenseEditHistory, '{"history": []}'::jsonb),
                    '{last_updated_by}',
                    $1::jsonb
                ),
                // 追加history数组元素
                '{history}',
                coalesce(expenseEditHistory->'history', '[]'::jsonb) || $2::jsonb
            )
        `)
    },
    {
        where: {
            id: {
                [Op.in]: expenseIDXasTableID,
            },
        },
        // 绑定参数，避免SQL注入
        bind: [req.user.id, editHistoryItem]
    }
);

方法二：使用嵌套的sequelize.fn #

await Expenses.update(
    {
        isDeleted: true,
        expenseEditHistory: sequelize.fn(
            'jsonb_set',
            // 第一步：更新last_updated_by
            sequelize.fn(
                'jsonb_set',
                sequelize.fn('coalesce', sequelize.col('expenseEditHistory'), sequelize.literal(`'{"history": []}'::jsonb`)),
                sequelize.literal(`'{last_updated_by}'`),
                sequelize.cast(req.user.id, 'jsonb')
            ),
            // 第二步：追加history数组元素
            sequelize.literal(`'{history}'`),
            sequelize.fn(
                '||',
                sequelize.fn('coalesce', sequelize.col('expenseEditHistory.history'), sequelize.literal(`'[]'::jsonb`)),
                sequelize.cast(editHistoryItem, 'jsonb')
            )
        )
    },
    {
        where: {
            id: {
                [Op.in]: expenseIDXasTableID,
            },
        }
    }
);

关键说明 #

• coalesce函数用于处理expenseEditHistory字段为null的情况，确保即使字段为空也能正常初始化并追加元素。
• ||操作符是PostgreSQL中JSONB数组的追加方式，比array_append更适配JSONB类型。
• 使用bind参数或cast方法确保数据类型正确转换为JSONB，同时避免SQL注入风险。

内容的提问来源于stack exchange，提问作者SaaD Ibne Jamal