How to get ids of parents based on deeply nested sub document?

Question

This question is in continuation with older question: Parent.save() not working when sub document / deeply nested document is modified

Say I have a document as below :

{
    "apiCallCount": 1,
    "_id": "5e0da052b4b3fe5188602e11",
    "email": "abc@def.net",
    "password": "123123",
    "userName": "username",
    "companyName": "companyName",
    "apiKey": "apiKey",
    "solutionType": "solutionType",
    "parentCompany": "parentCompany",
    "buildings": [
        {
            "gateways": [
                {
                    "devices": [
                        {
                            "_id": "5e0da052b4b3fe5188602e15",
                            "serialNumber": "serialNumber 1",
                            "area": "area",
                            "connectionStatus": 0,
                            "gatewayKey": "gatewayKey",
                            "applicationNumber": 11,
                            "firmwareVersion": "firmwareVersion",
                            "needsAttention": true,
                            "verificationCode": "123456",
                            "patientRiskStatus": "patientRiskStatus",
                            "patientFirstName": "UPDATED!!!",
                            "patientLastName": "patientLastName",
                            "createdAt": "2020-01-02T07:48:34.287Z",
                            "updatedAt": "2020-01-02T07:48:34.287Z"
                        },
                        {
                            "_id": "5e0da052b4b3fe5188602e14",
                            "serialNumber": "serialNumber 2",
                            "area": "area",
                            "connectionStatus": 0,
                            "gatewayKey": "gatewayKey",
                            "applicationNumber": 22,
                            "firmwareVersion": "firmwareVersion",
                            "needsAttention": true,
                            "verificationCode": "987654",
                            "patientRiskStatus": "patientRiskStatus",
                            "patientFirstName": "patientFirstName",
                            "patientLastName": "patientLastName",
                            "createdAt": "2020-01-02T07:48:34.288Z",
                            "updatedAt": "2020-01-02T07:48:34.288Z"
                        }
                    ],
                    "_id": "5e0da052b4b3fe5188602e13",
                    "gatewayName": "gatewayName 1",
                    "gatewayKey": "gatewayKey",
                    "suite": "suite",
                    "createdAt": "2020-01-02T07:48:34.288Z",
                    "updatedAt": "2020-01-02T07:48:34.288Z"
                }
            ],
            "_id": "5e0da052b4b3fe5188602e12",
            "buildingName": "buildingName 1",
            "address": "address",
            "suite": "suite",
            "floor": "floor",
            "timeZone": "String",
            "createdAt": "2020-01-02T07:48:34.288Z",
            "updatedAt": "2020-01-02T07:48:34.288Z"
        }
    ],
    "createdAt": "2020-01-02T07:48:34.289Z",
    "updatedAt": "2020-01-02T09:10:25.200Z",
    "__v": 0
}

I am able to dig through document and able to get device sub document with "verificationCode": "123456"

Now I want to get gatewayID(one level up) and buildingID(two level up) for this device.

Currently I have a call like this : I am trying to update parent doc based on deeply nested sub-document.

I get sub document by accountId and verification code like below. and then need to update the parent.

In my sample below I Put hard-coded ids which i need to get run time.

if (newlySavedUser) {
            try {
                let result = await Account.findByIdAndUpdate(
                    accountId,
                    {
                        $set: {
                            "buildings.$[building].gateways.$[gateway].devices.$[device].patientFirstName": "userName",
                            "buildings.$[building].gateways.$[gateway].devices.$[device].patientLastName": "userName1"
                        }
                    },
                    {
                        arrayFilters: [

                            { "building._id": ObjectId("5d254bb179584ebcbb68b712") },  /// <----  I want to get this buildingId
                            { "gateway._id": ObjectId("5d254b64ba574040d9632ada") }, /// <----  I want to get this gatewayId
                            { "device.verificationCode": "4144" } /// <-- based on this verificationCode
                        ],
                        new: true
                    }
                );

                if (!result) return res.status(404);
                console.log(result)
                //res.send(result);
            } catch (err) {
                console.log(err);
                res.status(500).send("Something went wrong");
            }
        }

Trying "tom slabbaert" solution for aggregate.

Account.aggregate([
                {
                    $unwind: "$buildings"
                },
                {
                    $unwind: "$gateways"
                },
                {
                    $match: {
                        "buildings.gateways.devices.verificationCode": "4144"
                    }
                },
                {
                    $project: {
                        buildingID: "$buildings._id",
                        gatewayID: "$gateways._id",
                    }
                }
            ]).exec((err, result)=>{
                console.log("result", result)
                if(err) throw err;
            });

Your help is appreciated.

Answer 1

A simple aggregation would suffice:

    db.collection.aggregate([
        {
            $unwind: "$buildings"
        },
        {
            $unwind: "$buildings.gateways"
        },
        {
            $match: {
                "buildings.
gateways.devices.verificationCode": "123456"
            }
        },
        {
            $project: {
                buildingID: "$buildings._id",
                gatewayID: "$gateways._id",
            }
        }
    ])

If its possible to get duplicates you can $group on buildingID to avoid that.

Answer 2

You can use this 3 level $unwind aggregation, and then match the document you want:

db.collection.aggregate([
  {
    $unwind: "$buildings"
  },
  {
    $unwind: "$buildings.gateways"
  },
  {
    $unwind: "$buildings.gateways.devices"
  },
  {
    $match: {
      "buildings._id": "5e0da052b4b3fe5188602e12",
      "buildings.gateways._id": "5e0da052b4b3fe5188602e13",
      "buildings.gateways.devices.verificationCode": "123456"
    }
  }
])

This will give you the following result:

[
  {
    "__v": 0,
    "_id": "5e0da052b4b3fe5188602e11",
    "apiCallCount": 1,
    "apiKey": "apiKey",
    "buildings": {
      "_id": "5e0da052b4b3fe5188602e12",
      "address": "address",
      "buildingName": "buildingName 1",
      "createdAt": "2020-01-02T07:48:34.288Z",
      "floor": "floor",
      "gateways": {
        "_id": "5e0da052b4b3fe5188602e13",
        "createdAt": "2020-01-02T07:48:34.288Z",
        "devices": {
          "_id": "5e0da052b4b3fe5188602e15",
          "applicationNumber": 11,
          "area": "area",
          "connectionStatus": 0,
          "createdAt": "2020-01-02T07:48:34.287Z",
          "firmwareVersion": "firmwareVersion",
          "gatewayKey": "gatewayKey",
          "needsAttention": true,
          "patientFirstName": "UPDATED!!!",
          "patientLastName": "patientLastName",
          "patientRiskStatus": "patientRiskStatus",
          "serialNumber": "serialNumber 1",
          "updatedAt": "2020-01-02T07:48:34.287Z",
          "verificationCode": "123456"
        },
        "gatewayKey": "gatewayKey",
        "gatewayName": "gatewayName 1",
        "suite": "suite",
        "updatedAt": "2020-01-02T07:48:34.288Z"
      },
      "suite": "suite",
      "timeZone": "String",
      "updatedAt": "2020-01-02T07:48:34.288Z"
    },
    "companyName": "companyName",
    "createdAt": "2020-01-02T07:48:34.289Z",
    "email": "abc@def.net",
    "parentCompany": "parentCompany",
    "password": "123123",
    "solutionType": "solutionType",
    "updatedAt": "2020-01-02T09:10:25.200Z",
    "userName": "username"
  }
]

Mongoplayground