Mongodb join String Id with ObjectId in aggregate lookup

edx

Member

506 Points

24 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.

.netcore c#mongodb mongodb-driver join

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

Share:

Answers

Brian

Moderator

2232 Points

14 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

None

2568 Points

74 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