如何查询Mongoose中含指定顺序子文档的Route模型文档？

需求说明 #

需要查询所有route数组中按顺序包含"KMME"和"ASN"这两个stationCode的Route文档。

原Mongoose Schema #

const routeSchema = new mongoose.Schema({
    route: {
        type: [{
            stationCode: {
                type: String,
                required: true,
                uppercase: true,
                validate: {
                    validator: async function(val) {
                        const doc = await Station.findOne({
                            code: val,
                        });
                        if (!doc) return false;
                        return true;
                    },
                    message: `A Station with code {VALUE} not found`,
                },
            },
            distanceFromOrigin: {
                type: Number,
                required: [
                    true,
                    'A station must have distance from origin, 0 for origin',
                ],
            },
        }, ],

        validate: {
            validator: function(val) {
                return val.length >= 2;
            },
            message: 'A Route must have at least two stops',
        },
    },
}, {
    toJSON: { virtuals: true },
    toObject: { virtuals: true }
});

示例文档 #

{
    "_id": {
        "$oid": "636957ce994af955df472ebc"
    },
    "route": [{
            "stationCode": "DHN",
            "distanceFromOrigin": 0,
            "_id": {
                "$oid": "636957ce994af955df472ebd"
            }
        },
        {
            "stationCode": "KMME",
            "distanceFromOrigin": 38,
            "_id": {
                "$oid": "636957ce994af955df472ebe"
            }
        },
        {
            "stationCode": "ASN",
            "distanceFromOrigin": 54,
            "_id": {
                "$oid": "636957ce994af955df472ebf"
            }
        }
    ],
    "__v": 0
}

解决方案 #

1. 直接查询语句（MongoDB原生/Mongoose） #

要实现按顺序匹配数组元素的需求，可使用MongoDB的$expr结合$indexOfArray操作符，判断"KMME"的索引小于"ASN"的索引，同时确保两个值都存在于数组中：

MongoDB原生查询

db.routes.find({
  $expr: {
    $and: [
      { $gt: [{ $indexOfArray: ["$route.stationCode", "ASN"] }, -1] },
      { $gt: [{ $indexOfArray: ["$route.stationCode", "KMME"] }, -1] },
      { $lt: [{ $indexOfArray: ["$route.stationCode", "KMME"] }, { $indexOfArray: ["$route.stationCode", "ASN"] }] }
    ]
  }
})

Mongoose写法

const routes = await Route.find({
  $expr: {
    $and: [
      { $gt: [{ $indexOfArray: ["$route.stationCode", "ASN"] }, -1] },
      { $gt: [{ $indexOfArray: ["$route.stationCode", "KMME"] }, -1] },
      { $lt: [{ $indexOfArray: ["$route.stationCode", "KMME"] }, { $indexOfArray: ["$route.stationCode", "ASN"] }] }
    ]
  }
});

2. Schema优化方案 #

如果这类顺序查询是高频操作，可以在Schema中新增一个派生字段stationCodes，存储路线的站点代码序列数组，简化查询逻辑并提升性能：

优化后的Schema

const routeSchema = new mongoose.Schema({
    route: {
        type: [{
            stationCode: {
                type: String,
                required: true,
                uppercase: true,
                validate: {
                    validator: async function(val) {
                        const doc = await Station.findOne({ code: val });
                        return !!doc;
                    },
                    message: `A Station with code {VALUE} not found`,
                },
            },
            distanceFromOrigin: {
                type: Number,
                required: [true, 'A station must have distance from origin, 0 for origin'],
            },
        }],
        validate: {
            validator: function(val) {
                return val.length >= 2;
            },
            message: 'A Route must have at least two stops',
        },
    },
    // 新增派生字段，直接存储站点代码序列
    stationCodes: {
        type: [String],
        required: true
    }
}, {
    toJSON: { virtuals: true },
    toObject: { virtuals: true }
});

// 预保存中间件：自动同步stationCodes字段
routeSchema.pre('save', function(next) {
    this.stationCodes = this.route.map(item => item.stationCode);
    next();
});

// 可选：更新操作中间件，确保修改route时同步更新stationCodes
routeSchema.pre(['updateOne', 'findOneAndUpdate'], function(next) {
    const update = this.getUpdate();
    if (update.route) {
        this.setUpdate({
            ...update,
            stationCodes: update.route.map(item => item.stationCode)
        });
    }
    next();
});

优化后的查询语句

有了stationCodes字段后，查询更简洁：

// MongoDB原生
db.routes.find({
  $expr: {
    $and: [
      { $gt: [{ $indexOfArray: ["$stationCodes", "ASN"] }, -1] },
      { $gt: [{ $indexOfArray: ["$stationCodes", "KMME"] }, -1] },
      { $lt: [{ $indexOfArray: ["$stationCodes", "KMME"] }, { $indexOfArray: ["$stationCodes", "ASN"] }] }
    ]
  }
})

// Mongoose写法
const routes = await Route.find({
  $expr: {
    $and: [
      { $gt: [{ $indexOfArray: ["$stationCodes", "ASN"] }, -1] },
      { $gt: [{ $indexOfArray: ["$stationCodes", "KMME"] }, -1] },
      { $lt: [{ $indexOfArray: ["$stationCodes", "KMME"] }, { $indexOfArray: ["$stationCodes", "ASN"] }] }
    ]
  }
});

还可以给stationCodes字段创建索引，进一步提升查询性能：

routeSchema.index({ stationCodes: 1 });

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