Mongodb Join on _id field from String to ObjectId
MongodbJoinMongodb QueryAggregation FrameworkLookupMongodb Problem Overview
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.
Mongodb Solutions
Solution 1 - Mongodb
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"
}}
])
Solution 2 - Mongodb
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
Solution 3 - Mongodb
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"
}}
])