Mongodb Join on _id field from String to ObjectId
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"
}}
])
This is not possible as of MongoDB 3.4. This feature has already been requested, but hasn't been implemented yet. Here are the corresponding tickets:
- SERVER-22781: Allow $lookup between ObjectId (_id.str) and string
- SERVER-24947: Need a type conversion mechanism for booleans, ISODates, ObjectID
For now you'll have to store userId as ObjectId
EDIT
The previous tickets were fixed in MongoDB 4.0. You can now achieve this with the folowing query:
db.user.aggregate([
{
"$project": {
"_id": {
"$toString": "$_id"
}
}
},
{
"$lookup": {
"from": "role",
"localField": "_id",
"foreignField": "userId",
"as": "role"
}
}
])
result:
[
{
"_id": "584aac38686860d502929b8b",
"role": [
{
"_id": ObjectId("584aaca6686860d502929b8d"),
"role": "Admin",
"userId": "584aac38686860d502929b8b"
}
]
}
]
try it online: mongoplayground.net/p/JoLPVIb1OLS
I think the previous answer has an error on the $toObjectId
case. The let
statement applies to the db collection on which the function aggregate
is called (i.e 'role') and not on the collection pointed by "from" (i.e 'user').
db.role.aggregate([
{ "$lookup": {
"let": { "userObjId": { "$toObjectId": "$userId" } },
"from": "user",
"pipeline": [
{ "$match": { "$expr": { "$eq": [ "$_id", "$$userObjId" ] } } }
],
"as": "userDetails"
}}
])
Or
db.role.aggregate([
{ "$project": { "userObjId": { "$toObjectId": "$userId" } } },
{ "$lookup": {
"localField": "userObjId",
"from": "user",
"foreignField": "$_id",
"as": "userDetails"
}}
])
And
db.user.aggregate([
{ "$project": { "userStrId": { "$toString": "$_id" }}},
{ "$lookup": {
"localField": "userStrId",
"from": "role",
"foreignField": "userId",
"as": "roleDetails"
}}
])