Mongodb Join _id field from String to ObjectId

Question

I have two collections

• User

  { "_id" : ObjectId("584aac38686860d502929b8b"), "name" : "John" } 

• Role

    { "_id" : ObjectId("584aaca6686860d502929b8d"), "role" : "Admin", "userId" : "584aac38686860d502929b8b" } 

I want to join this collection based on userId (in the collection role ) - _id (in user > collection).

I tried the following query:

 db.role.aggregate( { $lookup: { from: 'user', localField: 'userId', foreignField: '_id', as: 'output' } } ); 

This gives me the expected results while I store userId as an ObjectId. When my userId is a string, there are no results. Ps: I tried

foreignField: '_id'.valueOf ()

and

foreignField: '_id'.toString ()

. But the match / join fails based on the ObjectId fields.

Any help would be appreciated.

Answer 1

This is not possible with MongoDB 3.4. This feature has already been requested, but not yet implemented. Here are the relevant tickets:

• SERVER-22781: allow $ search between ObjectId (_id.str) and string
• SERVER-24947: need a type conversion mechanism for boolean values, ISODates, ObjectID

For now, you will have to store userId as ObjectId

---

EDIT

Previous tickets have been fixed in MongoDB 4.0. Now you can achieve this with the following 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 this online: mongoplayground.net/p/JoLPVIb1OLS

Answer 2

You can use the $toObjectId aggregation from mongodb 4.0, which converts the id string 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" }} ]) 

Answer 3

I think the previous answer has an error in the case of $ toObjectId. The let statement is applied to the database collection in which the aggregate function is called (ie, "Role"), and not to the collection pointed to by "from" (ie, "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" }} ]) 

As well as

 db.user.aggregate([ { "$project": { "userStrId": { "$toString": "$_id" }}}, { "$lookup": { "localField": "userStrId", "from": "role", "foreignField": "userId", "as": "roleDetails" }} ]) 

Answer 4

This is actually possible in mongodb <4.0.

You need to add a new field to the Role document, which is a userId converted to an ObjectID:

 db.role.aggregate({ { $addFields: { userobjectid: { $convert: { input: '$userId', to: 'objectId' } }, { $lookup: { from: 'user', localField: 'userobjectid', foreignField: '_id', as: 'output' } } });