How to join three collections without $unwind and get nested result based on a condition?

user_2064292 0
Viewed 49

Persons Collection:

[
  {
    "_id": ObjectId("5f3258cfbaaccedaa5dd2c96"),
    "gender": "male",
    "name": {
      "title": "mr",
      "first": "victor",
      "last": "pedersen"
    },
    "location": {
      "street": "2156 stenbjergvej",
      "city": "billum",
      "state": "nordjylland",
      "postcode": 56649        
    },
    "email": "victor.pedersen@example.com"
  }
]

PersonDetails Collection:

{
    "_id": ObjectId("5f3a91e68b1c26e68f9ed3ad"),
    "country": "India",
    "personid": ObjectId("5f3258cfbaaccedaa5dd2c96")
}

CountryDetails Collection:

{
    "_id": ObjectId("5f3fc2aa9532398a037ff7ae"),
    "country": "India",
    "continent": "Asia"
}

Assume that 1 person can have many persondetails and 1 persondetails could have many country details.

Query: Get Person, persondetails and countrydetails where continent is Asia.

The result should look like:

[{
    "_id": ObjectId("5f3258cfbaaccedaa5dd2c96"),
    "gender": "male",
    "name": {
        "title": "mr",
        "first": "victor",
        "last": "pedersen"
    },
    "location": {
        "street": "2156 stenbjergvej",
        "city": "billum",
        "state": "nordjylland",
        "postcode": 56649
    },
    "email": "victor.pedersen@example.com",
    "persondetail": [{
        "_id": ObjectId("5f3a91e68b1c26e68f9ed3ad"),
        "country": "India",
        "personid": "5f3258cfbaaccedaa5dd2c96",
        "countrydetail": [{
            "_id": ObjectId("5f3fc2aa9532398a037ff7ae"),
            "country": "India",
            "continent": "Asia"
        }]
    }]
}]

Note: This has to be with aggregate()

My failed attempt :

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 query doesn't work and even if it is made to work unwind will give me a flat structure. Which is opposite of what I am looking for.

aggregation-framework mongodb-query node.js mongodb nosql
1 Answers

You can nest your lookups using $lookup with custom pipeline:

{
    $lookup: {
       from: "persondetails",
       let: { person_id: "$_id" },
       pipeline: [
           {
               $match: {
                   $expr: { $eq: [ "$personid", "$$person_id" ] }
               }
           },
           {
               $lookup: {
                   from: "countrydetails",
                   localField: "country",
                   foreignField: "country",
                   as: "countrydetail",
               }
           }
       ],      
       as: "persondetail"
    }
}

Mongo Playground

user_62389770
Related