'MongoDB: Need to match two fields in a lookup
I've indexed nuit, regNumber and _id for a faster query.
You can find sample data and a solution which i'm not happy with because using $unwind and $lookup makes it iterate a million documents: https://mongoplayground.net/p/jR4Y-9-5As7
This is what i did:
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"
}
}
])
This is the result:
{
"_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")
}
]
}
]
}
The result i need, while matching nuit with _id, and packageId with packages._id from reservations and parks collections, respectively, is:
{
"_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")
}
}
}
Solution 1:[1]
From the data you gave us, you can do something like:
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"]}
}
}
}
}
}
}
}
])
Since park is an array and packages is a nested array, and according to your requested output you want it to stay that way, you can $map the external array (park) and use $filter to keep only elements inside packages that meet your condition.
Edit:
If it is guaranteed that there is only one expected package result, as been edited in the question, than this is a simpler case that does not require a a $filter inside a $map, but just a simple $arrayElemAt and 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"}
])
Solution 2:[2]
I ended up with https://mongoplayground.net/p/4UwwZrOaTJj. Thanks to @nimrod, i found out about $filter.
Because packages._id are all unique in parks collection, i've indexed it and filtered it. Then, i checked the nuit manually in js: if(nuit === lastInsertedReservation.park._id). This was the fastest query i could get using 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"}
])
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Iglesias Leonardo |