Let's say I have these two collections:
// Members:
{
"_id":{
"$oid":"60dca71f0394f430c8ca296d"
},
"church":"60dbb265a75a610d90b45c6b",
"name":"Julio Verne Cerqueira"
},
{
"_id":{
"$oid":"60dca71f0394f430c8ca29a8"
},
"nome":"Ryan Steel Oliveira",
"church":"60dbb265a75a610d90b45c6c"
}
And
// Churches
{
"_id": {
"$oid": "60dbb265a75a610d90b45c6c"
},
"name": "Saint Antoine Hill",
"active": true
},
{
"_id": {
"$oid": "60dbb265a75a610d90b45c6b"
},
"name": "Jackeline Hill",
"active": true
}
And I want to query it and have a result like this:
// Member with Church names
{
"_id":{
"$oid":"60dca71f0394f430c8ca296d"
},
"church":"Jackeline Hill",
"name":"Julio Verne Cerqueira"
},
{
"_id":{
"$oid":"60dca71f0394f430c8ca29a8"
},
"church":"Saint Antoine Hill",
"nome":"Ryan Steel Oliveira"
}
If I try a Lookup, I have the following Result: (It is getting the entire churches collection).
How would I do the query, so it gives me only the one church that member is related to?
And, if possible, how to Sort the result in alphabetical order by church then by name?
Obs.: MongoDB Version: 4.4.10
There is matching error in the $lookup
--> $pipeline
--> $match
.
It should be:
$match: {
$expr: {
$eq: [
"$_id",
"$$searchId"
]
}
}
From the provided documents, members
to churchies
relationship will be 1 to many. Hence, when you join members
with churchies
via $lookup
, the output church
will be an array with only one churchies
document.
Aggregation pipelines:
$lookup
- Join members
collection (by $$searchId
) with churchies
(by _id
).$unwind
- Deconstruct church
array field to multiple documents.$project
- Decorate output document.$sort
- Sort by church
and name
ascending.db.members.aggregate([
{
"$lookup": {
"from": "churchies",
"let": {
searchId: {
"$toObjectId": "$church"
}
},
"pipeline": [
{
$match: {
$expr: {
$eq: [
"$_id",
"$$searchId"
]
}
}
},
{
$project: {
name: 1
}
}
],
"as": "church"
}
},
{
"$unwind": "$church"
},
{
$project: {
_id: 1,
church: "$church.name",
name: 1
}
},
{
"$sort": {
"church": 1,
"name": 1
}
}
])