Mongodb Join on _id field from String to ObjectId
Solution 1
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 2
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 3
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"
}}
])
Kavya Mugali
Developer working on C# .Net, MVC, WCF and Node js. Learning AngularJs, Software Design, Application Security Keen on knowing and following best practices and industry standards.
Updated on July 09, 2022Comments
-
Kavya Mugali almost 2 years
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.
-
Nech over 5 yearsNote that these 2 aggregation operators are available only from 4.0
-
AJB over 5 years@AnthonyWinzlet This should now be the accepted answer.
-
felix about 4 yearsHi @Ashh, you have the wrong bracket in
cond
, this should work: mongoplayground.net/p/jKVt60tPtc6 -
teuber789 almost 3 yearsGood callout on the error in the previous answer's
let
statement. -
teuber789 almost 3 yearsThere's an error in the
$toObjectId
case. Gary Wild's answer fixes it.