如何用Sequelize关联PostgreSQL JSON数组中的用户ID与User表？

要实现通过Home.findAll()关联User表并获取用户email，核心是利用PostgreSQL的jsonb_array_elements函数展开Home表的JSON数组字段，再关联User表，最后聚合结果。以下是具体实现：

前提说明 #

• 确保Home表的users字段在Sequelize模型中定义为DataTypes.JSONB类型：

  users: {
    type: DataTypes.JSONB,
    allowNull: false,
    defaultValue: []
  }
  

实现代码 #

const homes = await Home.findAll({
  attributes: [
    'id',
    'name',
    // 聚合关联后的用户数据，包含原字段和新增的email
    [sequelize.literal(`
      json_agg(
        json_build_object(
          'userId', u.user->>'userId',
          'name', u.user->>'name',
          'email', users.email
        )
      )
    `), 'users']
  ],
  include: [{
    model: User,
    as: 'users',
    attributes: [], // 不需要单独返回User表字段，聚合到users数组里
    required: false, // 允许Home没有关联用户的情况
    on: sequelize.literal(`
      users.id = (u.user->>'userId')::uuid
    `),
    // 用cross join unnest展开JSON数组
    from: sequelize.literal(`
      home, jsonb_array_elements(home.users) as u(user)
    `)
  }],
  group: ['home.id', 'home.name'] // 按Home的主键分组，确保每条Home只返回一条
});

代码解释 #

• 展开JSON数组：通过jsonb_array_elements(home.users) as u(user)把Home的users数组拆成单行记录，每条对应一个用户对象。
• 关联User表：通过users.id = (u.user->>'userId')::uuid将拆出来的userId（转成uuid类型匹配User表的id）与User表关联，若User表id为整数则把::uuid改为::int。
• 聚合结果：用json_agg和json_build_object把关联后的用户信息（原userId、name加上email）重新拼成JSON数组，替换原来的users字段。
• 分组：按Home的id和name分组，避免因数组展开导致同一条Home出现多条结果。

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