本文介绍了mongodb汇总查询查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有具有以下值的集合:
I have collections with following values:
报告
{
"_id": { "$oid": "5f05e1d13e0f6637739e215b" },
"testReport": [
{
"name": "Calcium",
"value": "87",
"slug": "ca",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
},
{
"name": "Magnesium",
"value": "-98",
"slug": "mg",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
}
],
"patientName": "Patient Name",
"clinicName": "Clinic",
"gender": "Male",
"bloodGroup": "A",
"createdAt": { "$date": "2020-07-08T15:10:09.612Z" },
"updatedAt": { "$date": "2020-07-08T15:10:09.612Z" }
},
设置
{
"_id": { "$oid": "5efcba7503f4693d164e651d" },
"code": "Ca",
"codeLower": "ca",
"name": "Calcium",
"valueFrom": -75,
"valueTo": -51,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7503f4693d164e651e" }, // <=== should find this for Calcium
"code": "Ca",
"codeLower": "ca",
"name": "Calcium",
"valueFrom": 76,
"valueTo": 100,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7603f4693d164e65bb" }, // <=== should find this for Magnesium
"code": "Mg",
"codeLower": "mg",
"name": "Magnesium",
"valueFrom": -100,
"valueTo": -76,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7503f4693d164e6550" },
"code": "Mg",
"codeLower": "mg",
"name": "Magnesium",
"valueFrom": 76,
"valueTo": 100,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
}
我想从报告集合中搜索值，并检查该值是否在 setups 集合中，并返回 _id ，并在报告集合的 setupIds 字段中添加返回的_id.
I want to search the value from reports collection and check whether the value is in range from the setups collection and return the _id and add the returned _ids in setupIds field on reports collection.
我尝试使用以下聚合框架:
I tried with the following aggregation framework:
db.reports.aggegrate([
{
'$match': {
'_id': new ObjectId('5f05e1d13e0f6637739e215b')
}
}, {
'$lookup': {
'from': 'setups',
'let': {
'testValue': '$testReport.value',
'testName': '$testReport.name'
},
'pipeline': [
{
'$match': {
'$expr': {
{
'$and': [
{
'$eq': [
'$name', '$$testName'
]
}, {
'$gte': [
'$valueTo', '$$testValue'
]
}, {
'$lte': [
'$valueFrom', '$$testValue'
]
}
]
}
}
}
}
],
'as': 'setupIds'
}
}
])
此查询未找到预期结果.
This query didn't find the expected results.
这是我想要的更新的报告集合:
This is the updated reports collection I want:
{
"_id": { "$oid": "5f05e1d13e0f6637739e215b" },
"setupIds": [{ "$oid": "5efcba7503f4693d164e651e" }, { "$oid": "5efcba7603f4693d164e65bb" }], // <=== Here, array of the ObjectId (ref: "Setups")
"patientName": "Patient Name",
"clinicName": "Clinic",
"gender": "Male",
"bloodGroup": "A",
"createdAt": { "$date": "2020-07-08T15:10:09.612Z" },
"updatedAt": { "$date": "2020-07-08T15:10:09.612Z" }
},
推荐答案
您可以尝试以下操作
[{
$match: {
_id: ObjectId('5f05e1d13e0f6637739e215b')
}
}, {
$unwind: {
path: "$testReport"
}
}, {
$lookup: {
from: 'setup',
'let': {
testValue: {
$toInt: '$testReport.value'
},
testName: '$testReport.name'
},
pipeline: [{
$match: {
$expr: {
$and: [{
"$eq": [
"$name",
"$$testName"
]
},
{
"$gte": [
"$valueTo",
"$$testValue"
]
},
{
"$lte": [
"$valueFrom",
"$$testValue"
]
}
]
}
}
}],
as: 'setupIds'
}
}, {
$group: {
_id: "$_id",
patientName: {
$first: "$patientName"
},
clinicName: {
$first: "$clinicName"
},
gender: {
$first: "$gender"
},
bloodGroup: {
$first: "$bloodGroup"
},
createdAt: {
$first: "$createdAt"
},
updatedAt: {
$first: "$updatedAt"
},
setupIds: {
$addToSet: "$setupIds._id"
}
}
}, {
$addFields: {
setupIds: {
$reduce: {
input: "$setupIds",
initialValue: [],
in: {
$setUnion: ["$$this", "$$value"]
}
}
}
}
}]
工作蒙哥游乐场
这篇关于mongodb汇总查询查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！