How to $lookup by avoiding null values in mongodb aggregate

Question

In here i'm using $lookup to to a left join from other collections, the query works fine but when some records missing values it returns

errmsg : $in requires an array as a second argument, found: null

Heres the querying document structure :

{
 "no" : "2020921008981",
 "sale" : {
  "soldItems" : [
    {
        "itemId" : "5b55ac7f0550de00210a3b24", 
    },

    {
        "itemId" : "5b55ac7f0550de00215584re", 
    }
  ], 
 "bills" : [
    {
        "billNo" : "2020921053467", 
        "insurancePlanId" : "160", 
    },

    {
        "billNo" : "2020921053467", 
        "insurancePlanId" : "170", 
     }
   ],
   "visitIds" : [
   5b55ac7f0550de00210a3b24, 5b55ac7f0550de00210a3b24
   ]

  }
}

the query :

db.case.aggregate([
{
    $lookup: {
        from: "insurance",
        let: { ipids: "$sale.bill.insurancePlanId" },
        pipeline: [
            {
                $unwind: "$coveragePlans"
            },
            {
                $match: { $expr: { $in: ["$coveragePlans._id", "$$ipids"] } }
            },
            {
                $project: { _id: 0, name: 1 }
            }
        ],
        as: "insurances"
    }
},
{
    $lookup: {
        from: "item",
        let: { iid: "$salesOrder.purchaseItems.itemRefId" },
        pipeline: [
            {
                $match: {
                    $expr: {
                        $in: ["$_id", {
                            $map: {
                                input: "$$iid",
                                in: { $toObjectId: "$$this" }
                            }
                        }
                        ]
                    }
                }
            }
        ],
        as: "items"
      }
  }
])

insurance collection :

{ 
  "_id" : ObjectId("5b55aca20550de00210a6d25"), 
  "name" : "HIJKL" 
  "coveragePlans" : [
    {
      "_id" : "160", 
      "name" : "UVWZ", 
    }, 
    { 
    "_id" : "161", 
    "name" : "LMNO", 
    }
   ]
 },
{ 
  "_id" : ObjectId("5b55aca20550de00210a6d25"),  
  "name" : "WXYZ"
  "coveragePlans" : [
   {
    "_id" : "169", 
    "name" : "5ABC", 
   }, 
   { 
    "_id" : "170", 
    "name" : "4XYZ", 
    }
  ]
}

item collection :

{ 
  "_id" : ObjectId("5b55ac7f0550de00210a3b24"), 
  "code" : "ABCDE"
},
{ 
  "_id" : ObjectId("5b55ac7f0550de00215584re"), 
  "code" : "PQRST" 
}

How to avoid this and do null checks effectively before pipe-lining into the next stages? Tried with { $match: { "fieldName": { $exists: true, $ne: null } } } but it returns mongo error regarding the format. If its the way to go please mention the stage i should put that.. Thanks in advance

you seem to have a typo in your let variable : ipids: "$sales.bill.insurancePlanId", but 'sale' in your scheme — matthPen
– matthPen, Commented Apr 15, 2020 at 14:21
easiest thing to do would be to not set null when inserting the data into the database. instead set a blank array [] as the value in that field. — Dĵ ΝιΓΞΗΛψΚ
– Dĵ ΝιΓΞΗΛψΚ, Commented Apr 15, 2020 at 14:37
Đĵ ΝιΓΞΗΛψΚ, well thats a thing that i have no control of ;) — dinesh alwis
– dinesh alwis, Commented Apr 15, 2020 at 15:08

Valijon · Accepted Answer · 2020-04-16 08:09:59Z

2

You can use $ifNull operator

let: { ipids: {$ifNull:["$sale.bill.insurancePlanId", [] ]} },

EDIT: To skip empty "$salesOrder.purchaseItems.itemRefId" values

let: { iid: {$filter: {input:"$salesOrder.purchaseItems.itemRefId", cond:{$ne:["$$this", ""]}}} },

edited Apr 16, 2020 at 8:09

answered Apr 15, 2020 at 16:12

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

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

2 Comments

dinesh alwis Over a year ago

Thanks for the answer. the previous error went away, but now this occurs.

errmsg : Failed to parse objectId  in $convert with no onError value: Invalid string length for parsing to OID, expected 24 but found 0

Valijon Over a year ago

@dineshalwis I've updated my answer, update your second lookup iid variable

Joe · Accepted Answer · 2020-04-15 16:16:35Z

You can get around that by not using $in.

It looks like this $map is executed separately for every document in the items collection. If you were to run the map in an $addFields stage, you could used the simple form of lookup to match the added field to _id, which would automagically handle missing, null, and array.

Remove the added field with a $project stage if necessary.

db.case.aggregate([
    {$lookup: {
        from: "insurance",
        let: { ipids: "$sale.bill.insurancePlanId" },
        pipeline: [
            {$unwind: "$coveragePlans"},
            {$match: { $expr: { $in: ["$coveragePlans._id", "$$ipids"] } }},
            {$project: { _id: 0, name: 1 }}
        ],
        as: "insurances"
    }}
    {$addFields:{
        matchArray:{$map: {
                       input: "$$iid",
                       in: { $toObjectId: "$$this" }
        }}
    }},
    {$lookup: {
        from: "item",
        localField: "matchArray",
        foreignField:"_id",
        as: "items"
    }},
    {$project:{
        arrayField: 0 
    }}
])

Collectives™ on Stack Overflow

How to $lookup by avoiding null values in mongodb aggregate

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related