People Database:
[
{
"_id": ObjectId("5f3258cfbaaccedaa5dd2c96"),
"gender": "male",
"name": {
"title": "mr",
"first": "victor",
"last": "pedersen"
},
"location": {
"street": "2156 stenbjergvej",
"city": "billum",
"state": "nordjylland",
"postcode": 56649
},
"email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="fc8a959f88938ed28c9998998e8f9992bc99849d918c9099d29f9391">[email protected]</a>"
}
]
PersonDetails Database:
{
"_id": ObjectId("5f3a91e68b1c26e68f9ed3ad"),
"country": "India",
"personid": ObjectId("5f3258cfbaaccedaa5dd2c96")
}
CountryDetails Database:
{
"_id": ObjectId("5f3fc2aa9532398a037ff7ae"),
"country": "India",
"continent": "Asia"
}
One individual can have multiple person details and each person detail may have several country details.
Query: Retrieve Person, persondetails, and countrydetails for countries in Asia continent.
The output should be like:
[{
"_id": ObjectId("5f3258cfbaaccedaa5dd2c96"),
"gender": "male",
"name": {
"title": "mr",
"first": "victor",
"last": "pedersen"
},
"location": {
"street": "2156 stenbjergvej",
"city": "billum",
"state": "nordjylland",
"postcode": 56649
},
"email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="493f202a3d263b67392c2d2c3b3a2c27092c31282439252c672a2624">[email protected]</a>",
"persondetail": [{
"_id": ObjectId("5f3a91e68b1c26e68f9ed3ad"),
"country": "India",
"personid": "5f3258cfbaaccedaa5dd2c96",
"countrydetail": [{
"_id": ObjectId("5f3fc2aa9532398a037ff7ae"),
"country": "India",
"continent": "Asia"
}]
}]
}]
Note: This needs to be done with aggregate()
My unsuccessful try :
db.persons.aggregate([
{
"$match": {
"$expr": {
"$eq": [
"$_id",
{
"$toObjectId": "5f3258cfbaaccedaa5dd2c96"
}
]
}
}
},
{
$lookup: {
from: "persondetails",
localField: "_id",
foreignField: "personid",
as: "persondetail"
}
},
{
$unwind: {
"path": "$persondetail",
includeArrayIndex: "arrayIndex",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "country",
localField: "persondetail.country",
foreignField: "country",
as: "countrydetails"
}
},
{
"$match": {
"$expr": {
"$eq": [
"$persondetail.continent",
"Asia"
]
}
}
}
])
The above code does not execute as expected, and even if corrected, unwind would result in a flat structure, contrary to the desired outcome.