如何用Sequelize更新PostgreSQL中JSONB数组含指定值的用户数据？

针对PostgreSQL中JSONB类型的数组更新需求，我们可以结合PostgreSQL原生JSON操作函数与Sequelize实现批量移除指定元素的逻辑，以下是具体实现：

核心思路 #

1. 筛选出preferences.pets数组包含目标宠物的用户
2. 利用PostgreSQL的数组操作函数移除指定元素，再将结果写回JSONB字段

TypeScript函数实现 #

import { User } from './models'; // 替换为你的User模型实际导入路径
import { literal } from 'sequelize';

async function removePetFromAllUsers(pet: string) {
  // 执行批量更新
  const updateResult = await User.update(
    {
      preferences: literal(`jsonb_set(
        preferences, 
        '{pets}', 
        (array_remove(jsonb_array_text(preferences->'pets'), ?))::jsonb,
        true
      )`, [pet])
    },
    {
      where: {
        preferences: literal(`preferences->'pets' @> ?::jsonb`, [`["${pet}"]`])
      }
    }
  );

  return updateResult; // 返回更新行数等结果信息
}

代码细节说明 #

• 查询条件：使用PostgreSQL的@>操作符判断JSONB数组是否包含指定元素，确保只更新确实存在目标宠物的用户行
• 更新逻辑：
  1. jsonb_array_text(preferences->'pets')：将JSONB数组转换为PostgreSQL原生文本数组
  2. array_remove(..., pet)：移除文本数组中的指定宠物元素
  3. ::jsonb：将处理后的文本数组转回JSONB类型
  4. jsonb_set：将更新后的pets数组重新写入preferences对象，最后一个参数true兼容pets键不存在的边界情况
• 防SQL注入：通过Sequelize的参数绑定（?占位符）处理用户输入，避免直接字符串拼接带来的安全风险

模型定义参考 #

确保你的User模型正确声明preferences为JSONB类型：

import { Model, DataTypes } from 'sequelize';

export class User extends Model {
  public id!: number;
  public preferences!: { pets: string[]; cars?: string[] };
}

User.init(
  {
    preferences: {
      type: DataTypes.JSONB,
      allowNull: false,
      defaultValue: { pets: [], cars: [] }
    }
  },
  {
    sequelize, // 替换为你的Sequelize实例
    tableName: 'users'
  }
);

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