如何查询子文档数组中的元素

Question

这是路由模型的猫鼬模式

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
},
});

这个模式有一个字段route作为文档数组，每个文档都有一个stationCode，

我想查询所有包含"KMME"的文档和";ASN"以指定的顺序输入stationCode。下面是使用模式

创建文档的示例

{
"_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
}

请为这个问题提供一个查询或为这个问题提供另一个架构定义

Answer 1

一个简单的选项是:

db.collection.aggregate([
{$match: {$expr: {$setIsSubset: [["ASN", "KMME"], "$route.stationCode"]}}},
{$set: {
wanted: {$first:{
$filter: {
input: "$route",
cond: {$in: ["$$this.stationCode", ["ASN", "KMME"]]}
}
}}
}},
{$match: {"wanted.stationCode": "KMME"}},
{$unset: "wanted"}
])

看看它在操场的例子中是如何工作的