我已經建立了索引nuit，regNumber并_id進行了更快的查詢。
您可以找到我不滿意的示例資料和解決方案，因為使用$unwind并$lookup使其迭代一百萬個檔案：https ://mongoplayground.net/p/jR4Y-9-5As7
這就是我所做的：
db.reservations.aggregate([
{ $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
{ $sort: { _id: -1 } },
{ $limit: 1 },
{
$lookup: {
from: "parks",
localField: "packageId",
foreignField: "packages._id",
as: "park"
}
}
])
這是結果：
{
"_id" : ObjectId("6284c8915a5e7a6a6ec675c1"),
"nuit" : "400400400",
"packageId" : ObjectId("627bfee7c528a41f5fd74664"),
"regNumber" : "aha-720-mp",
"date" : {
"entry" : ISODate("2022-05-25T10:20:00Z"),
"exit" : ISODate("2022-05-29T10:20:00Z")
},
"park" : [
{
"_id" : "400400400",
"name" : "One Stop",
"spaces" : "50",
"createdAt" : ISODate("2022-05-11T14:25:44.816Z"),
"contacts" : {
"mainPhoneNumber" : "800000000",
"optionalPhoneNumber" : "800000000",
"province" : "1",
"district" : "matola a",
"avenue" : "filipe",
"quarterNumber" : "12",
"residenceNumber" : "1",
"floorNumber" : "1"
},
"packages" : [
{
"_id" : ObjectId("627bfc95834d42107eeefc93"),
"name" : "Basic One",
"isPeriodic" : 0,
"createdAt" : ISODate("2022-05-11T18:12:37.024Z"),
"articles" : [
{
"_id" : ObjectId("627e462842a6d1357a37613d"),
"period" : "00:00:00:01:00",
"price" : NumberDecimal("100")
},
{
"_id" : ObjectId("627e4ede3c0615d81a3783b3"),
"period" : "00:01:00:00:00",
"price" : NumberDecimal("1700")
},
{
"_id" : ObjectId("627e4f4f3c0615d81a3783b4"),
"period" : "00:00:00:01:30",
"price" : NumberDecimal("200.99")
},
{
"_id" : ObjectId("627e4f692ffd3313694bf88d"),
"period" : "00:00:01:20:00",
"price" : NumberDecimal("300.5")
}
]
},
{
"_id" : ObjectId("627bfee7c528a41f5fd74664"),
"name" : "Anual básico",
"isPeriodic" : 0,
"createdAt" : ISODate("2022-05-11T18:22:31.624Z")
},
{
"_id" : ObjectId("627c16b5b91ad0563d22f8eb"),
"name" : "básico",
"isPeriodic" : 0,
"createdAt" : ISODate("2022-05-11T20:04:05.462Z")
},
{
"_id" : ObjectId("627c16e028c58a28175e7327"),
"name" : "Anual",
"isPeriodic" : 0,
"createdAt" : ISODate("2022-05-11T20:04:48.863Z")
}
]
}
]
}
nuit我需要的結果，分別與_id、 和packageIdfrompackages._id和reservationscollectionsparks匹配時，是：
{
"_id": ObjectId("6284c8915a5e7a6a6ec675c1"),
"nuit": "400400400",
"packageId": ObjectId("627bfee7c528a41f5fd74664"),
"regNumber": "aha-720-mp",
"date": {
"entry": ISODate("2022-05-25T10:20:00Z"),
"exit": ISODate("2022-05-29T10:20:00Z")
},
"park":
{
"packages":
{
"_id": ObjectId("627bfee7c528a41f5fd74664"),
"name": "Anual básico",
"isPeriodic": 0,
"createdAt": ISODate("2022-05-11T18:22:31.624Z")
}
}
}
uj5u.com熱心網友回復：
根據您提供給我們的資料，您可以執行以下操作：
db.collection.aggregate([
{ $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
{ $sort: { _id: -1 } },
{ $limit: 1 },
{
$lookup: {
from: "parks",
localField: "nuit",
foreignField: "_id",
as: "park"
}
},
{
$set: {
park: {
$map: {
input: "$park",
as: "elem",
in: {
packages: {
$filter: {
input: "$$elem.packages",
as: "item",
cond: {$eq: ["$$item._id", "$packageId"]}
}
}
}
}
}
}
}
])
mongoDB 操場。
由于park是一個陣列并且packages是一個嵌套陣列，并且根據您要求的輸出，您希望它保持這種狀態，您可以$map使用外部陣列（park）并使用$filter僅保留packages滿足您條件的元素。
編輯：如果保證只有一個預期package結果，如問題中所編輯，那么這是一個更簡單的情況，不需要 a$filter內的aa $map，而只是一個簡單$arrayElemAt的 a $filter：
db.collection.aggregate([
{ $match: { nuit: '400400400', regNumber: 'aha-720-mp' } },
{ $sort: { _id: -1 } },
{ $limit: 1 },
{
$lookup: {
from: "parks",
localField: "nuit",
foreignField: "_id",
as: "park"
}
},
{$set: {res: {$arrayElemAt: ["$park", 0]}, park: null}},
{
$set: {
"park.packages": {
$filter: {
input: "$res.packages",
as: "item",
cond: {$eq: [ "$$item._id", "$packageId"]}
}
},
res: null
}
},
{$set: {'park.packages': {$arrayElemAt: [ "$park.packages", 0]}}},
{$unset: "res", "park.packages.articles"}
])
操場
uj5u.com熱心網友回復：
我最終得到了https://mongoplayground.net/p/4UwwZrOaTJj。感謝@nimrod，我發現了關于$filter.
因為packages._id在集合中都是唯一的parks，所以我已經對其進行了索引并對其進行了過濾。然后，我nuit在 js: 中手動檢查了if(nuit === lastInsertedReservation.park._id)。這是我可以使用的最快的查詢explain(true)。
db.reservations.aggregate([
{$match: {nuit: "400400400",regNumber: "aha-720-mp"}},
{$sort: {_id: -1}},
{$limit: 1},
{
$lookup: {
from: "parks",
let: {
pid: "$packageId"
},
localField: "packageId",
foreignField: "packages._id",
pipeline: [
{
$project: {
packages: {
$filter: {
input: "$packages",
as: "package",
cond: {$eq: ["$$package._id","$$pid"]}
},
}
}
},
{$project: {packages: {"articles": 0}}
},
{$unwind: "$packages"},
],
as: "park"
}
},
{$unwind: "$park"}
])
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/477725.html