javascript - 无法使用 node.js 根据 MongoDB 中的特定字符串对值进行分组?
问题描述
我有一个用例,其中有 3 个集合。即,模板、组和每个模板的 1-动态集合,它们将在模板创建后立即创建。
每个模板由一个 Groups 集合组成,devices
每个模板由device
一个groupId
Groups 集合组成。动态集合存储模板设备推送的数据。
以下是我收藏的样本数据。
模板数据
{
"_id": "5e0ae38729218b0a3861118b",
"templateId": "13435158964",
"devices": [
{
"deviceId": "a-1",
"_id": "5e0ae49629218b0a3861118f",
"group": "5e0ae41d29218b0a3861118d",
},{
"deviceId": "sb-0001",
"_id": "5e0af166981f39410cd89b72",
"group": "5e0af11d981f39410cd89b70"
}]
}
动态收集数据
[
{
"_id": "5e0ae793b1384737a4f855cf",
"template": "13435158964",
"deviceId": "a-1",
"heat": 30,
"humidity": 40
},
{
"_id": "5e0ae7a2b1384737a4f855d0",
"template": "13435158964",
"deviceId": "sb-0001",
"heat": 40,
"humidity": 20
},
{
"_id": "5e0ae890b1384737a4f855d3",
"template": "13435158964",
"deviceId": "a-1",
"heat": 10,
"humidity": 20
},
{
"_id": "5e0af188981f39410cd89b73",
"template": "13435158964",
"deviceId": "a-1",
"heat": 60,
"humidity": 50
},
{
"_id": "5e0af196981f39410cd89b74",
"template": "13435158964",
"deviceId": "sb-0001",
"heat": 15,
"humidity": 25
}]
组数据
[{
"_id": "5e0af11d981f39410cd89b70",
"template": "5e0ae38729218b0a3861118b",
"groupName": "Flats"
},{
"_id": "5e0ae41d29218b0a3861118d",
"template": "5e0ae38729218b0a3861118b",
"groupName": "Swimming Pool"
}]
因此,对于上述数据,我试图获得groupName
指定的热量和湿度的总和。所以预期的输出应该是:
预期产出
[
{
"_id": "Swimming Pool",
"heat": 110,
"humidity": 165,
"count":2
},{
"_id": "Flat",
"heat": 70,
"humidity": 80,
"count":1
}]
**返回的输出**
[
{
"_id": "Swimming Pool",
"heat": 180,
"humidity": 245,
"count":2
},{
"_id": "Flat",
"heat": 180,
"humidity": 245,
"count":1
}]
我不知道如何实现这一点,任何人都可以帮我解决这个问题
我的查询: 在这里,模板和组是不同的集合。
db.getCollection('13435158964').aggregate([
{ "$match": {"entryDayTime":{
$lt: new Date("2019-11-29T18:30:00.000Z"),
$gte: new Date("2019-10-31T18:30:00.000Z")
}
}
},{
$lookup:{
from:"templates",
localField:"template",
foreignField:"templateId",
as:"templateData"
}
},{
$unwind:"$templateData"
},{
$unwind:"$templateData.devices"
},{
$lookup:{
from:"groups",
localField:"templateData.devices.group",
foreignField:"_id",
as:"groupData"
}
},{
$unwind:"$groupData"
},{
$group:{
_id: "$groupData.groupName",
heat:{$sum:"$heat"},
humidity:{$sum:"$humidity"},
count:{$sum:1}
}
},{
$project:{
"count":1,"heat":1,"humidity":1,"templateData.devices.group":1,"templateData.devices.deviceId":1,"groupData.groupName":1
}
}])
解决方案
据我了解,您期望按组名分组的数据。但是你得到了错误的结果。
因此,请参考以下聚合代码。
db.getCollection('13435158964').aggregate([
{
$lookup: {
from: "templates",
localField: "template",
foreignField: "templateId",
as: "templateData"
}
},
{
$unwind: "$templateData"
},
{
$unwind: "$templateData.devices"
},
{
$match: {
$expr: {
$eq: [
"$deviceId",
"$templateData.devices.deviceId"
]
}
}
},
{
$lookup: {
from: "groups",
localField: "templateData.devices.group",
foreignField: "_id",
as: "groupData"
}
},
{
$unwind: "$groupData"
},
{
$group: {
_id: "$groupData.groupName",
heat: {
"$sum": "$heat"
},
humidity: {
"$sum": "$humidity"
},
count: {
"$sum": 1
}
}
}
])
你会得到这样的答案,
[
{
"_id": "Swimming Pool",
"count": 3,
"heat": 100,
"humidity": 110
},
{
"_id": "Flats",
"count": 2,
"heat": 55,
"humidity": 45
}
]
你也可以参考这个链接, https://mongoplayground.net/p/hr95U1eT5bL
推荐阅读
- reactjs - 如何将 if 条件放入选择值中?使用本机反应
- linux - How to create a script that opens multiple terminals and runs different programs on them?
- android - 过滤 Java(Android) com ParseQuery campo Tipo 指针
- amazon-web-services - Terraform 更新现有 S3 配置
- assembly - x86 HLA 程序集中的指针
- flutter - 使用颤动在暗模式下切换图像
- reactjs - 找不到文件:'index.js' 与磁盘上的相应名称不匹配:'.\node_modules\Victory\es\victory'
- electron - 电子隐藏工作窗口无法正常工作
- javascript - 在reactjs中给定特定条件获取数组的值
- regex - 正则表达式匹配大括号之间的特定字符串