MongoDb:使用 $lookup 查找深度嵌套的对象 [英] MongoDb: find deeply nested object with $lookup
问题描述
我有这样的收藏:集合名称是 - 帐户.它有子文档，如帐户>建筑物>网关>设备.
I have collection like this: collection name is - account. and it has sub-documents like account > buildings > gateways > devices.
{
"_id" : ObjectId("5e1fe45cd05bfb0cc549297d"),
"apiCallCount" : 0,
"email" : "info@data.com",
"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 运算符查找匹配的设备对象.
I have accountId, buildingId, gatewayId, deviceId. I am trying to find matched device object using $lookup operator.
我想我必须首先使用 buildingId 找到建筑物对象，然后使用 gatewayId 过滤该建筑物下的网关，然后使用我拥有的 deviceId 查找设备对象.
I think I have to fist find the building object by using buildingId and then filter gateway under that building using gatewayId and then find device object using deviceId that I have.
我基本上需要访问设备对象字段才能在最终输出中进行投影.
难以为 usingg 查找运算符提出正确的管道.
I basically need to have access to device object fields to project in final output.
Having difficulty in coming up with correct pipleline for usingg lookup operator.
到目前为止我有这个:
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 的帐户文档.但我需要前往建筑物 > 网关 > 设备并找到匹配的设备.
accountDetails gives me access the account document based on accountId. but I need to get to buildings > gateways > devices and find the matching device.
更新:
我忘了说，我在这里处理 2 个系列.感知结果和帐户.
I forgot to mention, I am working with 2 collections here. sensingresults and accounts.
主要目的是聚合来自sensingresults的数据，同时也从account collection中找到deviceId并返回结果.
Main purpose is to aggregate data from sensingresults, but also find deviceId from account collection and return with the result.
这就是为什么需要查找来加入 2 个集合?
That's why lookup is needed to join 2 collections?
更新2:
当前输出:
{
"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
}
推荐答案
您可以使用 $filter, $arrayElemAt 和 $让:
You can find nested device using $filter, $arrayElemAt and $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 ] }
}
}
}
}
这篇关于MongoDb:使用 $lookup 查找深度嵌套的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
