How can i aggregate two collections if i have the index in multidimensional array?

//collection 1 item example:
{ 
    _id: ObjectId('000'),
    trips: [ // first array
        { // trip 1
            details: [ // array in first array
                { // detail 1
                    key: ObjectId('111') // ==> _id i want aggregate
                }
            ]
        }
    ]
}


//collection 2 item exampe:
{ 
    _id: ObjectId('111'),
    description: 'any info here...'
}

what is the procedure to do in order to obtain a valid result? I don't want to reprocess every data after doing a "find".
This is the result I would like to get

//collection 1 after aggregate:
{ 
    _id: ObjectId('000'),
    trips: [
        { // trip 1
            details: [
                { // detail 1
                    key: ObjectId('111') // id i want aggregate
                    _description_obj: {
                        _id: ObjectId('111'),
                        description: 'any info here...'
                    }
                }
            ]
        }
    ]
}

CodePudding user response：

Here's one way you could do it.

db.coll1.aggregate([
  {
    "$match": {
      _id: ObjectId("000000000000000000000000")
    }
  },
  {
    "$lookup": {
      "from": "coll2",
      "localField": "trips.details.key",
      "foreignField": "_id",
      "as": "coll2"
    }
  },
  {
    "$set": {
      "trips": {
        "$map": {
          "input": "$trips",
          "as": "trip",
          "in": {
            "$mergeObjects": [
              "$$trip",
              {
                "details": {
                  "$map": {
                    "input": "$$trip.details",
                    "as": "detail",
                    "in": {
                      "$mergeObjects": [
                        "$$detail",
                        {
                          "_description_obj": {
                            "$first": {
                              "$filter": {
                                "input": "$coll2",
                                "cond": {"$eq": ["$$this._id", "$$detail.key"]}
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      },
      "coll2": "$$REMOVE"
    }
  }
])

Try it on mongoplayground.net.