如何通过避免MongoDB聚合中的空值来进行$ lookup [英] How to $lookup by avoiding null values in mongodb aggregate

问题描述

在这里我使用$ lookup到其他集合的左连接中，查询工作正常，但是当某些记录缺少值时返回

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
   ]

  }
}

查询:

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"
      }
  }
])

保险收款:

{ 
  "_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", 
    }
  ]
}

项目集合:

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

如何避免这种情况，并在进入下一阶段之前进行有效的空检查?用{ $match: { "fieldName": { $exists: true, $ne: null } } }尝试过，但它返回有关格式的mongo错误.如果要走的路，请提一下我应该放的那个阶段..预先感谢

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

推荐答案

您可以使用

编辑:跳过空的"$salesOrder.purchaseItems.itemRefId"值

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

这篇关于如何通过避免MongoDB聚合中的空值来进行$ lookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！