this is the example of 'Routes' document

[{
routes: [{
  driver_id: "61e9536f142f337c96883c52",
  route_id:"SRT006",
  payment:13.2
}, {
  driver_id: "61e9536f142f337c96800a56",
  route_id:"SRT005",
  payment:15
}, {
  route_id:"SRT004",
  payment:20
}],
apartment: 'abc apartment',
deliverydate: "06/03/2022"
}]

this is the example of 'Users' document

[
{
  _id: ObjectId(61e9536f142f337c96883c52),
  name: 'John',
  phone: (222) 123-4568
},
{
  _id: ObjectId(61e9536f142f337c96800a56),
  name: 'Bob',
  phone: (200) 123-4568
}
]

Final results should be like this

[{
routes: [{
  driver_id: "61e9536f142f337c96883c52",
  driver_name: 'John',
  route_id:"SRT006",
  payment:13.2
}, {
  driver_id: "61e9536f142f337c96800a56",
  driver_name: 'Bob',
  route_id:"SRT005",
  payment:15
}, {
  route_id:"SRT004",
  payment:20
}],
apartment: 'abc apartment',
deliverydate: "06/03/2022"
}]

I have tried $lookup to add driver_name but getting errors. Any advice on what is the approach to getting the above results on MongoDB?

CodePudding user response：

1. $lookup - routes collection join with users collection via pipeline.

   1.1. $in - Filter the document with _id (converted to string type) is in routes_driver_id array.

   1.2. $project - Decorate output document(s) to be returned in the driver array.

2. $set - Set routes field.

   2.1. $map - Iterate each element in the routes array and returns a new array.

   2.1.1. $mergeObjects - Merge current iterated document with the result 2.1.1.1.

   2.1.1.1. $first - Get the first value of the array from 2.1.1.1.1.

   2.1.1.1.1. $filter - Filter the driver document by matching current route's _id with driver_id (converted to string type).

3. $unset - Remove driver field.

db.routes.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "driver",
      "let": {
        routes_driver_id: "$routes.driver_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                {
                  $toString: "$_id"
                },
                "$$routes_driver_id"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            driver_id: "$_id",
            name: 1
          }
        }
      ]
    }
  },
  {
    $set: {
      routes: {
        $map: {
          input: "$routes",
          as: "route",
          in: {
            $mergeObjects: [
              "$$route",
              {
                $first: {
                  $filter: {
                    input: "$driver",
                    cond: {
                      $eq: [
                        "$$route.driver_id",
                        {
                          $toString: "$$this.driver_id"
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "driver"
  }
])

Demo @ Mongo Playground

CodePudding user response：

The datatypes in your two collections are different, that's why it might be failing. driver_id is a string within Routes collection, but in users it's an ObjectId. Try this:

db.routes.aggregate([
  {
    "$unwind": "$routes"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "routes.driver_id",
      "foreignField": "_id",
      "as": "driver"
    }
  },
  {
    "$addFields": {
      "driver": {
        "$arrayElemAt": [
          "$driver",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "routes.driver_name": "$driver.name"
    }
  },
  {
    "$project": {
      driver: 0
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "routes": {
        "$push": "$routes"
      }
    }
  }
])

Here's the playground link. In this, we use ObjectId, in both collections. We unwind the routes array, perform the lookup and then group again on the basis of _id.