Mongodb join String Id with ObjectId in aggregate lookup

edx
edx
70 Points
25 Posts

I have two collections

User

{
  "_id" : ObjectId("5f51dac5ea43442fa419ca1f"),
  "name" : "John b"
}

Role

{
  "_id" : ObjectId("584aaca6686860d502929b8d"),
  "role" : "Admin",
  "userId" : "5f51dac5ea43442fa419ca1f"
}

I want to join two collection based on the userId (in role collection as string) with _id ( in user collection as ObjectId).

I'm trying something (with MongoDB.Driver):

mongoCollection.Aggregate()
        .Match(filter)
        .Lookup("user", "userId", "_id", @as: "user")
        .Unwind("user")
        .As<UserRole>()

MongoDB Query:

db.role.aggregate({
  "$lookup": {
    "from": "user",
    "localField": "userId",
    "foreignField": "_id",
    "as": "output"
  }
})

Query getting success but getting zero result.

Views: 179
Total Answered: 2
Total Marked As Answer: 1
Posted On: 11-Sep-2020 05:35

Share:   fb twitter linkedin
Answers
Brian
Brian
Participant
614 Points
82 Posts
         

Try following by using $toObjectId aggregation from mongodb 4.0 which converts String id to ObjectId:

db.role.aggregate({
  { "addFields": { "userObjectId": { "$toObjectId": "$userId" }}},
  "$lookup": {
    "from": "user",
    "localField": "userObjectId",
    "foreignField": "_id",
    "as": "output"
  }
})
Posted On: 13-Sep-2020 22:27
Smith
Smith
Participant
532 Points
131 Posts
         

Use following code in mongodb driver in c#

BsonDocument expression = new BsonDocument(new List<BsonElement>() {
            new BsonElement("userObjectId", new BsonDocument(new BsonElement("$toObjectId", "$userId")))
                });
BsonDocument addFieldsStage = new BsonDocument(new BsonElement("$addFields", expression));

mongoCollection.Aggregate()
        .Match(filter)
        .AppendStage<UserRole>(addFieldsStage)
        .Lookup("user", "userObjectId", "_id", @as: "user")
        .Unwind("user")
        .As<UserRole>()
        .ToEnumerable()
Posted On: 17-Sep-2020 04:12
Thanks.
 - edx  17-Sep-2020 09:45
 Log In to Chat