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.
我基本上需要访问设备对象字段才能在最终输出中进行投影.
使用查找运算符很难找到正确的pipleline.
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.
到目前为止，我已经拥有了:
I have this so far:
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.
主要目的是聚合来自感测结果的数据，但也要从帐户收集中找到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?
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
}
推荐答案
您可以使用 $ 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 ] }
}
}
}
}
这篇关于MongoDb:使用$ lookup查找深度嵌套的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
