我有这样的收藏：收藏名称是-account。并且包含子文档，例如帐户>建筑物>网关>设备。
{
"_id" : ObjectId("5e1fe45cd05bfb0cc549297d"),
"apiCallCount" : 0,
"email" : "[email protected]",
"password" : "dummy",
"userName" : "AAAAA",
"companyName" : "The AAAAAA",
"apiKey" : "5e1fe45cd05bfb0cc549297c",
"solutionType" : "VVVVVV",
"parentCompany" : "",
"buildings" : [
{
"_id" : ObjectId("5e1fe5e3d05bfb0cc5494146"),
"buildingName" : "xxxxxx",
"address" : "xxx",
"suite" : "101",
"floor" : "22",
"timeZone" : "us/eastern",
"gateways" : [
{
"_id" : ObjectId("5e1fe624d05bfb0cc549453a"),
"gatewayName" : "CC-GW-THF-001",
"gatewayKey" : "gk_5e1fe624d05bfb0cc549453a",
"suite" : "area1",
"devices" : [
{
"_id" : ObjectId("5e1fe751d05bfb0cc549578d"),
"serialNumber" : "129300000013",
"area" : "area1",
"connectionStatus" : 1,
"gatewayKey" : "gk_5e1fe624d05bfb0cc549453a",
"applicationNumber" : 30,
"firmwareVersion" : "1.0",
"needsAttention" : false,
"verificationCode" : "GAAS",
"createdAt" : ISODate("2020-01-16T04:32:17.899Z"),
"updatedAt" : ISODate("2020-01-16T08:53:54.460Z")
}
],
"createdAt" : ISODate("2020-01-16T04:27:16.678Z"),
"updatedAt" : ISODate("2020-01-16T08:53:54.460Z")
},
{
"_id" : ObjectId("5e1fe651d05bfb0cc54947f0"),
"gatewayName" : "AA-GW-THF-002",
"gatewayKey" : "gk_5e1fe651d05bfb0cc54947f0",
"suite" : "area2",
"devices" : [
{
"_id" : ObjectId("5e1fe7a9d05bfb0cc5495cdf"),
"serialNumber" : "129300000012",
"area" : "area2",
"connectionStatus" : 0,
"gatewayKey" : "gk_5e1fe651d05bfb0cc54947f0",
"applicationNumber" : 30,
"firmwareVersion" : "1.0",
"needsAttention" : false,
"verificationCode" : "VG3K",
"createdAt" : ISODate("2020-01-16T04:33:45.698Z"),
"updatedAt" : ISODate("2020-01-16T08:54:17.604Z")
}
],
"createdAt" : ISODate("2020-01-16T04:28:01.532Z"),
"updatedAt" : ISODate("2020-01-16T08:54:17.604Z")
},
],
"createdAt" : ISODate("2020-01-16T04:26:11.941Z"),
"updatedAt" : ISODate("2020-01-16T08:56:32.657Z")
}
],
"createdAt" : ISODate("2020-01-16T04:19:40.310Z"),
"updatedAt" : ISODate("2020-04-06T18:18:39.628Z"),
"__v" : 1,
}
我有accountId，buildingId，gatewayId，deviceId。我正在尝试使用$ lookup运算符查找匹配的设备对象。
我想我必须先使用buildingId查找建筑物对象，然后使用gatewayId在该建筑物下过滤网关，然后使用我拥有的deviceId查找设备对象。
我基本上需要访问设备对象字段才能在最终输出中进行投影。
使用查找运算符很难找到正确的pipleline。
到目前为止，我有：
db.getCollection('test').aggregate([
{
$lookup: {
from: 'account',
let: {
accountId: "$accountId"
},
pipeline: [
{
"$match": {
"$expr": {
"$eq": ["$_id", "$$accountId"]
}
}
},
],
as: "accountDetails"
}
}, {
$unwind: "$accountDetails"
}, {
$lookup: {
from: 'account',
let: {
accountId: "$accountId",
buildingId: "$buildingId",
buildings: "$accountDetails"
},
pipeline: [
{
"$match": {
"$expr": {
"$eq": ["$buildings._id", "$$buildingId"] // how to dig through nested document to get to devices ?
}
}
},
],
as: "buildingDetails"
}
}
{
$project: { ... ...
}
])
如果我这样做：
{
$lookup: {
from: 'account',
localField: "accountId",
foreignField: "_id",
as: "accountDetails"
}
},
accountDetails使我可以基于accountId访问帐户文档。但我需要进入建筑物>网关>设备并找到匹配的设备。
更新：
我忘了提，我在这里处理2个收藏。感测结果和帐户。
主要目的是从感测结果中汇总数据，还从帐户收集中找到deviceId并返回结果。
这就是为什么需要查找才能加入2个收藏集的原因？
UPDATE2：
电流输出：
{
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"companyName": "The AAAAAA",
"createdAt": ISODate("1970-01-01T00:00:00Z"),
"dateHour": "2020-03-19T18",
"deviceId": ObjectId("5e1fe81ed05bfb0cc5496406"),
"gatewayId": ObjectId("5e1fe6a6d05bfb0cc5494d25"),
"minuteBucket": 1
}
预期结果：
{
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"createdAt": ISODate("1970-01-01T00:00:00Z"),
"dateHour": "2020-03-19T18",
"deviceId": ObjectId("5e1fe81ed05bfb0cc5496406"),
"gatewayId": ObjectId("5e1fe6a6d05bfb0cc5494d25"),
"minuteBucket": 1,
"serialNumber: 1, // this value should come from device object
"area": 1 // this value should come from device object
}
您可以device使用$ filter，$ arrayElemAt和$ let来嵌套：
device: {
$let: {
vars: {
building: {
$arrayElemAt: [ { $filter: { input: "$company_name.buildings", cond: { $eq: [ "$$this._id", "$buildingId" ] }} }, 0 ]
}
},
in: {
$let: {
vars: {
gateway: {
$arrayElemAt: [ { $filter: { input: "$$building.gateways", cond: { $eq: [ "$$this._id", "$gatewayId" ] }} }, 0 ]
}
},
in: { $arrayElemAt: [ { $filter: { input: "$$gateway.devices", cond: { $eq: [ "$$this._id", "$deviceId" ] }} }, 0 ] }
}
}
}
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句