mongoose - MongoDb: find deeply nested object with $lookup

Question

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" : "[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,
}

I have accountId, buildingId, gatewayId, deviceId. I am trying to find matched device object using $lookup operator.

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.

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: { ... ... 
              }
        ])

if I do this:

{
    $lookup: {
        from: 'account',
        localField: "accountId",
        foreignField: "_id",
        as: "accountDetails"
    }
},

accountDetails gives me access the account document based on accountId. but I need to get to buildings > gateways > devices and find the matching device.

UPDATE:

I forgot to mention, I am working with 2 collections here. sensingresults and accounts.

Main purpose is to aggregate data from sensingresults, but also find deviceId from account collection and return with the result.

That's why lookup is needed to join 2 collections?

UPDATE2:

Current output:

  {
    "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
  }

Expected result:

{
    "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 
  }

Answer 1

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 ] }
            }
        }
    }
}

Full Solution