Need a workaround for lookup of a string to objectID foreignField

Question

I'm new to mongodb and currently I'm facing this problem,

db.medical_records.aggregate([
    {
        "$group": {
            "_id": {
                "disease_id": "$disease_id" //a string
            }, "count": { "$sum": 1 }
        }
    },
    {
        "$addFields": {
            "disease_id": { "$toObjectId": "$disease_id" } 
            // i tried to change it into objectID so i could $lookup it
        }
    },
    {
        "$lookup": {
            "from": "diseases", 
            "localField": "disease_id", 
            "foreignField": "_id", 
            "as": "disease"
        }
    }
])

this is an example of my medical record collection

{
    "_id" : ObjectId("5989c8f13f3958120800682e"),
    "disease_id" : "5989c8f13f3958120800682f",
    "patient_id" : "5989c8f13f3958120800681f"
}

disease collection

{
    "_id" : ObjectId("5989c8f13f3958120800682f"),
    "name" : "Culpa autem officia.",
    "code" : "Est aperiam."
}

and the result I expect is kind of,

{
    "_id" : {disease_id: 5989c8f13f3958120800682f},
    "count" : 20,
    "disease" : {
        "_id" : ObjectId("5989c8f13f3958120800682f"),
        "name" : "Culpa autem officia.",
        "code" : "Est aperiam."
    }
}

I need to join my medical record collection to disease collection as queried above.

When I tried to lookup it to disease collection it failed as foreignField is not the same type as the localField. I've been trying for some time to find a workaround on this problem. And the query above returned another error,

Unrecognized expression '$toObjectId'

This problem might have been asked several times, but I really need a workaround on this problem, please help

Can you update your question to include: (1) a sample document from the medical_records collection; (2) a sample document from the diseases collection; (3) a sample document showing your desired result. — glytching, Aug 08 '17 at 16:29
edit: after some more googling i realize that $toObjectId is non-existent aggregator, so that error is the expected result. — Muhammad Maulana, Aug 09 '17 at 01:24

score 1 · Answer 1 · answered Aug 17 '18 at 22:42

New in 4.0: https://docs.mongodb.com/manual/reference/operator/aggregation/toObjectId/

// Define stage to add convertedId field with converted _id value

idConversionStage = {
   $addFields: {
      convertedId: { $toObjectId: "$_id" }
   }
};

// Define stage to sort documents by the converted qty values

sortStage = {
   $sort: { "convertedId": -1 }
};


db.orders.aggregate( [
   idConversionStage,
   sortStage
])

dnickless · Answer 2 · 2019-10-22T06:59:38.040

Answer only valid for versions < 4 of MongoDB:

This cannot be done with you current data structure.

Also see here: Mongoose $lookup where localField is a string of an ObjectId in foreignField

And here: Is it possible to compare string with ObjectId via $lookup

However, why don't you change the data in your medical record collection to this:

{
    "_id" : ObjectId("5989c8f13f3958120800682e"),
    "disease_id" : ObjectId("5989c8f13f3958120800682f"), // note the ObjectId
    "patient_id" : ObjectId("5989c8f13f3958120800681f") // note the ObjectId
}

Given this format you can get what you want using the following query:

db.medical_records.aggregate([
    {
        "$group": {
            "_id": {
                "disease_id": "$disease_id" //a string
            }, "count": { "$sum": 1 }
        }
    },
    {
        "$lookup": {
            "from": "diseases", 
            "localField": "_id.disease_id", 
            "foreignField": "_id", 
            "as": "disease"
        }
    }
])

EDIT based on your comment below:

There is no $toObjectId operator in MongoDB. Try searching the documentation and you'll find nothing! So there simply is no way to achieve your goal without changing your data. I do not know the "eloquent laravel-mongodb" framework you're mentioning but based on its documentation I am thinking your models might need some tuning. How do you model your relationship right now?

actually I'm using a framework library (eloquent laravel-mongodb) whom save my data as it is. But then for more complex query I'm forced to use aggregate and I couldn't do much. What I am currently asking is how to use $toObjectId correctly? if not because that error above it might become a workaround for this case — Muhammad Maulana, Aug 09 '17 at 01:12

score 0 · Answer 3 · answered Mar 02 '19 at 20:04

Whew... After going through all the docs and stackoverflow answers, I will like to recommend a simple fix.

When saving your users_id or any document Object id in another collection make sure the dataType remains ObjectId or cast it to ObjectId before saving the document.

Then using the documentation on Mongodb alone without trying to cast or bind.

db.orders.aggregate([
   {
     $lookup:
       {
         from: "users",
         localField: "user_id",
         foreignField: "_id",
         as: "inventory_docs"
       }
  }
]) 

This case user_id is coming from your current model then _id is coming from your users collections which both are already ObjectId by Origin.

The entire point of the question itself is not to do what you just answered — , Oct 24 '20 at 09:47

score 0 · Answer 4 · answered Mar 02 '19 at 20:13

When saving a ref to a new collection for example. user_id in orders collection. make sure the dataType is ObjectID. then you can use the ObjectID without having to convert. For example vehicles and drivers collection. by default aggregate will match dataType to dataType this also maintain the speed of your query.


 await Vehicle.aggregate([{
    $lookup: {
      from: 'users', 
      localField: "driver_id", 
      foreignField: "_id", 
      as: "users"
    }
  }]).then(vehicles => {
    data.status   = 200;
    data.message  = "Vehicles retreived!";
    data.data     = vehicles;
  }).catch(err => {
    // console log value
    console.log(err);
    data.status   = 500;
    data.message  = "Error retreiving vehicle";
    data.data     = JSON.stringify(err);
  });

Need a workaround for lookup of a string to objectID foreignField

4 Answers4

Linked