MongoDB - lookup - multiple collection - Result in one array

Question

Site collection fields - _id, name

Node collection fields - _id, siteId, name

Device collection fields - _id, nodeId, name

Sensor collection fields - _id, deviceId, name

I am new with MongoDB, Expected Result (without duplicated data):

[{
        "_id": "608aa9bd323489617cfe2081",
        "name": "Site One two 3",
        "node": [{
            "name": "Node Guj 3 222 ",
            "device": [{
                "mode": 1,
                "siteId": "608aa9bd323489617cfe2081",
                "nodeId": "608aa9cc323489617cfe2083",
                "isActive": true,
                "_id": "608aa9ee323489617cfe2084",
                "sensor": [{
                    "_id": "608aa9ee323489617cfe2085",
                    "name": "NVR_Channel1"
                }]
            }]
        }]
    },
    {
        "_id": "608aa9bd323489617cfe2083",
        "name": "Site One two 22",
        "node": [{
            "name": "Node Guj 3 222 ",
            "device": []
        }]
    },
    {
        "_id": "608aa9bd323489617cfe2085",
        "name": "SiteO",
        "node": [{
            "name": "Node22 ",
            "device": [{
                "mode": 1,
                "siteId": "608aa9bd323489617cfe2081",
                "nodeId": "608aa9cc323489617cfe2083",
                "isActive": true,
                "_id": "608aa9ee323489617cfe2084",
                "sensor": []
            }]
        }]
    }
]

As I mentioned earlier I am new at MongoDB, I tried the below query but getting not expected result

const result = await Site.aggregate([
        {
          $lookup:
          {
            from: "nodes",
            localField: "_id",
            foreignField: "siteId",
            as: "node"
          }
        },
        {
          $lookup:
          {
            from: "devices",
            localField: "node._id",
            foreignField: "nodeId",
            as: "device"
          }
        },
        {
          $lookup:
          {
            from: "sensors",
            localField: "node.device._id",
            foreignField: "deviceId",
            as: "sensor"
          }
        }
      ]);

Below is the current response where node, device, sensor are coming at same level and expected is site -> node -> device -> sensor.

[{
    "_id": "608aab1016be1c11dfe77422",
    "name": "Aivid_Site_One",
    "node": [{
            "_id": "608b9072932b3c0cc5ab4fd0",
            "name": "no sensort 11"
        },
        {
            "_id": "608b9073932b3c0cc5ab4fd1",
            "name": "no sensort 11"
        }
    ],
    "device": [{
        "_id": "608b9091932b3c0cc5ab4fd2",
        "name": "3nd floor Device"
    }],
    "sensor": [{
        "_id": "608b9091932b3c0cc5ab4f33",
        "name": "sensor 1"
    }]
}]

Answer 1

You can try lookup with pipeline,

• $lookup with nodes and pass siteId to pipeline
• $match siteId condition
• $lookup with devices and pass deviceId to pipeline
• $match deviceId condition
• $lookup with sensors collection

db.sites.aggregate([
  {
    $lookup: {
      from: "nodes",
      let: { siteId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$siteId", "$siteId"] } } },
        {
          $lookup: {
            from: "devices",
            let: { nodeId: "$_id" },
            pipeline: [
              { $match: { $expr: { $eq: ["$$nodeId", "$nodeId"] } } },
              {
                $lookup: {
                  from: "sensors",
                  localField: "_id",
                  foreignField: "deviceId",
                  as: "sensors"
                }
              }
            ],
            as: "devices"
          }
        }
      ],
      as: "nodes"
    }
  }
])

Playground