如何将含数组字段处理的嵌套SQL查询转换为Sequelize实现？

核心疑问结论 #

• 涉及unnest()数组展开的原生SQL转换，必须使用Sequelize literal。Sequelize内置的查询操作符没有封装PostgreSQL数组的unnest展开逻辑，无法完全通过内置方法组合实现你写的三层嵌套子查询。

对应实现代码 #

写法1：单条SQL完全对齐原语句（literal实现） #

直接把嵌套子查询部分用literal包裹，推荐用参数绑定写法避免SQL注入：

const { Op, literal } = require('@sequelize/core');
// 引入你已定义的模型
// const City = require('./models/city');

async function queryCities(infoId, minLocation) {
  return City.findAll({
    where: {
      id: {
        [Op.in]: literal(`(
          SELECT DISTINCT "id" FROM (
            SELECT "id", unnest(types) as "typeId" FROM "Cities"
          ) cityTypes
          WHERE "typeId" IN (
            SELECT "typeId" FROM "Info"
            WHERE "infoId" = ? AND "location" >= ?
          )
        )`, [infoId, minLocation])
      }
    },
    order: [['id', 'ASC']]
  });
}

写法2：拆两次查询（几乎不用literal，性能更优） #

你的原SQL本质是筛选types数组中包含任意一个「指定infoId、location达标对应的typeId」的城市记录。如果不强制要求单条SQL执行，可以用PostgreSQL数组重叠操作符&&（对应Sequelize内置的Op.overlap）实现，逻辑完全等价：

const { Op } = require('@sequelize/core');

async function queryCitiesSimplified(infoId, minLocation) {
  // 第一步：先查出符合条件的typeId集合
  const validTypeIds = (await Info.findAll({
    attributes: ['typeId'],
    where: {
      infoId,
      location: { [Op.gte]: minLocation }
    },
    raw: true
  })).map(item => item.typeId);

  // 第二步：直接查types数组和validTypeIds有交集的城市
  return City.findAll({
    where: {
      types: { [Op.overlap]: validTypeIds }
    },
    order: [['id', 'ASC']]
  });
}

注意事项 #

• 上述两种写法都针对PostgreSQL数据库，unnest、数组类型、Op.overlap都是PostgreSQL专属特性，MySQL等其他数据库不支持。
• 如果坚持用单条SQL，unnest(types)这部分逻辑没有办法用Sequelize内置方法替代，必须通过literal传入原生SQL片段。

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