MongoDB: lookup into nested foreign field

Question

I have the following scenario:

A collection of guests who need to choose which bus stop they will take the bus from. Then I have the busescollection. Each bus have multiple trips, and each trip has multiple stops, and each stop has a location.

I need to make a mongo query which returns the guest info with the bus stop they chose (bus stop info + populated location).

The problem is that trips, stops and location are not mongo documents, but subdocuments from buses.

guests collection:

{
  _id: ObjectId,
  name: String,
  ...
  bus_stop: ObjectId
  ...
}

buses collection:

{
  _id: ObjectId,
  ...
  trips: [{ //<-- Subdocument
    _id: ObjectId
    stops: [{ //<-- Subdocument
      _id: ObjectId,
      time: Date,
      location: { //<-- Subdocument
        _id: ObjectId,
        ...
        name: String,
        coordinates: [Number]
      }
    }]
  }]
}

What I need:

{
  _id: ObjectId,
  name: String,
  ...
  bus_stop: {
    _id: ObjectId,
    time: Date,
    location: {
      _id: ObjectId,
      ...
      name: String,
      coordinates: [Number]
    }
  }
}

I'm trying to perform a $lookup, but no luck so far. This is the query I'm trying to run:

$lookup: {
  from: "buses",
  let: { stop_id: "$bus_stop" },
  pipeline: [
    { $match: { $expr: { $in: [ "$$stop_id", "$going_trips.$stops._id" ] } } } // trying to filter out the bus containing the bus stop
    ... // more stuff should probably go here
  ],
  as: "bus_stop",
},

This is incomplete and wrong, but I'm stuck here on this step trying to filter out the buses which contains the bus stop in question. After this, I should probably figure out how to get the bus stop ans location info.

Any help would be appreciated.

Answer 1

https://mongoplayground.net/p/8Z5Lp8QG4gY

db.guests.aggregate([
  {
    "$lookup": {
      from: "buses",
      let: {
        stop_id: "$bus_stop"
      },
      pipeline: [
        {
          $unwind: {
            path: "$trips"
          }
        },
        {
          $unwind: {
            path: "$trips.stops"
          }
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$stop_id",
                "$trips.stops._id"
              ]
            },
            
          }
        }
      ],
      as: "bus_stopDetails",
      
    }
  }
])

you could do something like this here i did first unwind the trips and stops then checked the stopID, you need to tweak this based on your need and this is working solution but i feel it can be optimized further by tuning query if needed.