mongodb - 如何通过避免 mongodb 聚合中的空值来查找 $lookup
问题描述
在这里,我使用 $lookup 到其他集合的左连接,查询工作正常,但是当某些记录缺少值时它返回
errmsg : $in requires an array as a second argument, found: null
这是查询文档结构:
{
"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 错误。如果要走的路,请提及我应该放的阶段..提前致谢
解决方案
您可以使用$ifNull运算符
let: { ipids: {$ifNull:["$sale.bill.insurancePlanId", [] ]} },
编辑:跳过空"$salesOrder.purchaseItems.itemRefId"
值
let: { iid: {$filter: {input:"$salesOrder.purchaseItems.itemRefId", cond:{$ne:["$$this", ""]}}} },
推荐阅读
- php - 仅在选择选项时在特定位置显示值
- lookup - 未找到查找 [lookup_table] - Apache Druid
- flutter - Graphql返回具有一组键值对的突变的类型名错误
- javascript - 行分组时有没有办法关闭嵌套行数?
- r - R访问引用类对象列表中的字段值
- reactjs - 我的摩纳哥编辑器实例无法更新
- javascript - 按下按钮后显示来自 javascript 变量的数据
- jenkins - Groovy 脚本以提取以给定名称开头的作业
- java - @RabbitListener 无法从同一个 docker 容器连接到 rabbitMQ
- javascript - 如何将我的 ajax 请求合并为一行?