How to convert string to objectId in LocalField for $lookup Mongodb

mongodb join lookup
Saurabh Sharma picture Saurabh Sharma · Jan 17, 2017 · Viewed 8.6k times · Source

I want to add join collections using $lookup in mongodb. I am trying as below

{
 $lookup:{
   from:"User",
   localField:"assignedId",
   foreignField:"_id",
   as:"dataa"}
}

Now I have two collections

User contains objectid of users like "_id" : ObjectId("56ab6663d69d2d1100c074db"),

and Tasks where it contains assignedId as a string "assignedId":"56ab6663d69d2d1100c074db"

Now, when applying $lookup in both collection its not working because Id's are not matching.

For that I googled it and found a solution that to include

{ $project: { assignedId: {$toObjectId: "$assignedId"} }}

but this solution is not working for me, Its throwing an error:

assert: command failed: { "ok" : 0, "errmsg" : "invalid operator '$toObjectId'", "code" : 15999 } : aggregate failed

Please help me how can I resolve this issue.

Thanks

Answer

HoefMeistert picture HoefMeistert · Jan 17, 2017

It's not possible in the aggregation pipeline. There is no method to convert the type. Can you change the type of "assignedId" in the Tasks collection to ObjectId ? Else you have to do it in code, convert the ObjectId to a String and use in in another query.

Related questions

Mongodb Join on _id field from String to ObjectId

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) …

Read More
How do I perform the SQL Join equivalent in MongoDB?

How do I perform the SQL Join equivalent in MongoDB? For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each. comments { uid:12345, pid:444, …

Read More
How to join multiple collections with $lookup in mongodb

I want to join more than two collections in MongoDB using the aggregate $lookup. Is it possible to join? Give me some examples. Here I have three collections: users: { "_id" : ObjectId("5684f3c454b1fd6926c324fd"), "email" : "admin@gmail.…

Read More