How to avoid possible null error scenarios in mongodb Aggregate

Question

I've set up a fairly long mongo aggregate query to join several mongo collections together and shape up them into output of set of string fields. The query works fine as long as all the required values (ie : ids) exists but it breaks when it encounters null or empty values when doing the $lookup.

Following is the patientFile collection thats being queried :

 {
    "no" : "2020921008981",
    "startDateTime" : ISODate("2020-04-01T05:19:02.263+0000")
    "saleId" : "5e8424464475140d19c6941b",
    "patientId" : "5e8424464475140d1955941b"
 }

sale collection :

 {
    "_id" : ObjectId("5e8424464475140d19c6941b"),
    "invoices" : [
        {
          "billNumber" : "2020921053467", 
          "type" : "CREDIT",
          "insurancePlanId" : "160"
        },
        {
          "billNumber" : "2020921053469", 
          "type" : "DEBIT",
          "insurancePlanId" : "161"
        }
    ],
     "status" : "COMPLETE"
 }

insurance collection :

  { 
    "_id" : ObjectId("5b55aca20550de00210a6d25"), 
    "name" : "HIJKL" 
    "plans" : [
       {
         "_id" : "160", 
         "name" : "UVWZ", 
       }, 
       { 
         "_id" : "161", 
         "name" : "LMNO", 
       }
     ]
  }

patient collection :

{ 
  "_id" : ObjectId("5b55cc5c0550de00217ae0f3"),  
  "name" : "TAN NAI",
  "userId" : {
    "number" : "787333128H"
    }
}

Heres the aggregate query :

  db.getCollection("patientFile").aggregate([
  { $match: { "startDateTime": { $gte: ISODate("2020-01-01T00:00:00.000Z"), 
                          $lt: ISODate("2020-05-01T00:00:00.000Z") } } },

  {
    $lookup:
        {
            from: "patient",
            let: { pid: "$patientId" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$_id", { $toObjectId: "$$pid" }]
                        }
                    }
                },
                { "$project": { "name": 1, "userId.number": 1, "_id": 0 } }
            ],
            as: "patient"
        }
   },
   {
    $lookup:
        {
            from: "sale",
            let: { sid: "$saleId" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$_id", { $toObjectId: "$$sid" }]
                        }
                    }
                }
            ],
            as: "sale"
        }
},

{ $unwind: "$sale" },
{ $unwind: "$patient" },

{
    $lookup: {
        from: "insurance",
        let: { pid: {$ifNull:["$sale.bill.insurancePlanId", [] ]} },
        pipeline: [
            {
                $unwind: "$plans"
            },
            {
                $match: { $expr: { $in: ["$plans._id", "$$pid"] } }
            },
            {
                $project: { _id: 0, name: 1 }
            }
        ],
        as: "insurances"
       }
  },

  { $match: { "insurances.name": { $exists: true, $ne: null } } },


  {
    $addFields: {

        invoice: {
            $reduce: {
                input:  {$ifNull:["$sale.bill.billNumber", [] ]},
                initialValue: "",
                in: {
                    $cond: [{ "$eq": ["$$value", ""] }, "$$this", { $concat: ["$$value", "\n", "$$this"] }]
                }
            }
        },
        insurances: {
            $reduce: {
                input:  {$ifNull:["$insurances.name", [] ]},
                initialValue: "",
                in: {
                    $cond: [{ "$eq": ["$$value", ""] }, "$$this", { $concat: ["$$value", "\n", "$$this"] }]
                }
             }
          }

       }
  },


  {
    "$project": {
        "startDateTime": 1,
        "patientName": "$patient.name",
        "invoice": 1,
        "insurances": 1
       }
     }

   ],

      { allowDiskUse: true }

 )

Error :

Unable to execute the selected commands

Mongo Server error (MongoCommandException): Command failed with error 241 (ConversionFailure): 'Failed to parse objectId '' in $convert with no onError value: Invalid string length for parsing to OID, expected 24 but found 0' on server localhost:27017. 

The full response is:
 { 
    "ok" : 0.0, 
    "errmsg" : "Failed to parse objectId '' in $convert with no onError value: Invalid string length for parsing to OID, expected 24 but found 0", 
    "code" : NumberInt(241), 
    "codeName" : "ConversionFailure"
 }

As a solution i have found, used $ifNull but this error keeps coming. What would be the best step to take for this scenario?

Wai Ha Lee · Accepted Answer · 2020-04-16 12:35:41Z

5

I see a couple of ways:

Instead of converting the string value to an ObjectId to test, convert the ObjectId to a string
```
$match: {
    $expr: {
        $eq: [{$toString: "$_id"}, "$$pid" ]
    }
}
```

Instead of the $toObjectId helper, use $convert and provide onError and/or onNull values:

$match: {
    $expr: {
        $eq: ["$_id", { $convert: {
                               input: "$$pid",
                               to: "objectId", 
                               onError: {error:true},
                               onNull: {isnull:true}
        }}]
    }
}

edited Apr 16, 2020 at 12:35

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Apr 16, 2020 at 7:07

Joe

29k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dinesh alwis Over a year ago

I tried the first one it seems to be working fine. The issue is it takes much time. Is there anyway to optimize this aggregate query?

Joe Over a year ago

The pipeline form of lookup doesn't use indexes very efficiently. If you were to convert the strings to objectId in an $addFields stage, you could use the localField/foreignField form of lookup that uses indexes much better.

dinesh alwis Over a year ago

I see. And how about if i get only the required fields from the joined collection, so i can reduce the data thats being passed through?

Joe Over a year ago

That's also a good idea. It's not easy to get details about what is going on in a lookup pipeline, so you'll have to try both on a sizeable data set to see how they perform.

Collectives™ on Stack Overflow

How to avoid possible null error scenarios in mongodb Aggregate

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related