mongodb - 如何避免 mongodb Aggregate 中可能出现的 null 错误情况
问题描述
我已经设置了一个相当长的 mongo 聚合查询来将几个 mongo 集合连接在一起,并将它们塑造成一组字符串字段的输出。只要存在所有必需的值(即:id),查询就可以正常工作,但是在执行 $lookup 时遇到空值或空值时它会中断。
以下是正在查询的患者文件集合:
{
"no" : "2020921008981",
"startDateTime" : ISODate("2020-04-01T05:19:02.263+0000")
"saleId" : "5e8424464475140d19c6941b",
"patientId" : "5e8424464475140d1955941b"
}
销售收藏:
{
"_id" : ObjectId("5e8424464475140d19c6941b"),
"invoices" : [
{
"billNumber" : "2020921053467",
"type" : "CREDIT",
"insurancePlanId" : "160"
},
{
"billNumber" : "2020921053469",
"type" : "DEBIT",
"insurancePlanId" : "161"
}
],
"status" : "COMPLETE"
}
保险收取:
{
"_id" : ObjectId("5b55aca20550de00210a6d25"),
"name" : "HIJKL"
"plans" : [
{
"_id" : "160",
"name" : "UVWZ",
},
{
"_id" : "161",
"name" : "LMNO",
}
]
}
患者采集:
{
"_id" : ObjectId("5b55cc5c0550de00217ae0f3"),
"name" : "TAN NAI",
"userId" : {
"number" : "787333128H"
}
}
这是聚合查询:
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 }
)
错误 :
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"
}
作为我找到的解决方案,已使用$ifNull
但此错误不断出现。在这种情况下采取的最佳步骤是什么?
解决方案
我看到了几种方法:
不要将字符串值转换为 ObjectId 进行测试,而是将 ObjectId 转换为字符串
$match: { $expr: { $eq: [{$toString: "$_id"}, "$$pid" ] } }
代替
$toObjectId
助手,使用$convert
和提供onError
和/或onNull
值:$match: { $expr: { $eq: ["$_id", { $convert: { input: "$$pid", to: "objectId", onError: {error:true}, onNull: {isnull:true} }}] } }
推荐阅读
- css - 更改 WooCommerce 单品中的自定义折扣价格百分比样式
- javascript - 当用户键入输入栏时通过表格过滤
- python - 我的函数不会从我的字符串列表中读取高低天气数据
- r - 在 R 中进行模糊匹配的更有效方法?
- javascript - 如何在给定 DB FS 的情况下计算 DB SPL
- ruby-on-rails - 如何修复 Rails + Heroku 中未定义的方法错误
- scala - If else 在单个语句中:scala
- kubernetes - 使用 Kubernetes 为 Openfaas 的具有相同命名空间的 pod 设置配额
- java - 尝试更改片段背景时出现 NullPointerException(空对象引用上的 setBackgroundResource)
- java - Mockito.`when`(....) 正在调用实际实现。?