mongodb aggregation with multidimensional array

Question

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...'
                    }
                }
            ]
        }
    ]
}

rickhg12hs · Accepted Answer · 2022-11-24 11:15:46Z

2

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.

answered Nov 24, 2022 at 11:15

rickhg12hs

12k6 gold badges31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmytro V Over a year ago

works perfectly and exactly as I needed. I would never have done it alone. A thousand thanks!

rickhg12hs Over a year ago

@DmytroV Yeah, deeply nested arrays/objects can be a bit tedious.

Collectives™ on Stack Overflow

mongodb aggregation with multidimensional array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related