I have two collections
User
{
"_id" : ObjectId("584aac38686860d502929b8b"),
"name" : "John"
}
Role
{
"_id" : ObjectId("584aaca6686860d502929b8d"),
"role" : "Admin",
"userId" : "584aac38686860d502929b8b"
}
I want to join these collection based on the userId (in role collection) - _id ( in user collection).
I tried the below query:
db.role.aggregate({
"$lookup": {
"from": "user",
"localField": "userId",
"foreignField": "_id",
"as": "output"
}
})
This gives me expected results as long as i store userId as a ObjectId. When my userId is a string there are no results. Ps: I tried
foreignField: '_id'.valueOf()
and
foreignField: '_id'.toString()
. But no luck to match/join based on a ObjectId-string fields.
Any help will be appreciated.
You can use $toObjectId
aggregation from mongodb 4.0 which converts String id
to ObjectId
db.role.aggregate([
{ "$lookup": {
"from": "user",
"let": { "userId": "$_id" },
"pipeline": [
{ "$addFields": { "userId": { "$toObjectId": "$userId" }}},
{ "$match": { "$expr": { "$eq": [ "$userId", "$$userId" ] } } }
],
"as": "output"
}}
])
Or you can use $toString
aggregation from mongodb 4.0 which converts ObjectId
to String
db.role.aggregate([
{ "$addFields": { "userId": { "$toString": "$_id" }}},
{ "$lookup": {
"from": "user",
"localField": "userId",
"foreignField": "userId",
"as": "output"
}}
])