Find a single field from a nested Array in MongoDB

Question

Here I have a sample Nested Array. I have a problem with writing proper queries on this collection which is deeply nested.

{
"productUUID" : "craft001",
"providers": [
{
  "providerUUID": "prov001",
  "orgs": [
    {
      "orgUUID": "org001",
      "location": {
        "buildings": [
          {
            "buildingUUID": "sit001",
            "floors": [
              {
                "floorUUID": "GrndFlr",
                "assets": [ ],
                "agents": [ ],
                "users": [ ]
              },
              {
                "floorUUID": "1stFlr",
                "assets": [ ],
                "agents": [ ],
                "users": [ ]
              }
            ]
          },
          {
            "buildingUUID": "ist001",
            "floors": [ ]
          }
        ]
      }
    },
    {
      "orgUUID": "org002",
      "location": {
        "buildings": [ ]
      }
    }
  ]
},
{
  "providerUUID": "prov002",
  "orgs": [ ]
}
]
}

Question in simple words, "1. Get all orgUUIDs which fall under providerUUID: "prov001"". Similarly, "2. Get all floorUUIDs where "buildingUUID": "sit001"".

If someone can help me with the 1st ques, I hope I can solve the 2nd ques myself.

Answer 1

---

For some reason, I had to change the data in collection as following.

{
"productUUID": "prod001",
"providers": [
{
  "providerUUID": "prov001",
  "orgs": [
    {
      "orgUUID": "org001",
      "floors": [
        { "floorUUID": "SIT_GrndFlr" },
        { "floorUUID": "SIT_1stFlr" }
      ],
      "assets": [{},{}],
      "agents": [{},{}],
      "users": [{},{}]
    },
    {
      "orgUUID": "org002",
      "floors": [
        { "floorUUID": "IST_1stFlr" },
        { "floorUUID": "IST_2ndFlr" }
      ],
      "assets": [{},{}],
      "agents": [{},{}],
      "users": [{},{}]
    }
  ]
},
{
  "providerUUID": "prov002",
  "orgs": [
    {
      "orgUUID": "org001",
      "floors": [{},{}],
      "assets": [{},{}],
      "agents": [{},{}],
      "users": [{},{}]
    },
    {
      "orgUUID": "org002",
      "floors": [{},{}],
      "assets": [{},{}],
      "agents": [{},{}],
      "users": [{},{}]
    }
  ]
}
]
}

so, now with the help of @yogesh, I was introduced to aggregate and was able to write queries for my questions.

1. Get all `orgUUID`s under `providerUUID: "prov001"`.
db.collectionName.aggregate({"$unwind":"$providers"},
                        {"$match":{"providers.providerUUID":"prov001"}},
                        {"$project":{"orgUUID":"$providers.orgs.orgUUID"}},
                        {"$unwind":"$orgUUID"},
                        {"$project":{"_id":0,"orgUUID":1}}
                       )

2. Get all `floorUUID`s under `orgUUID : "org001"`.
db.collectionName.aggregate(
    { "$unwind" : "$providers" },    
    { "$match" : { "providers.providerUUID" : "prov001" } },    
    { "$unwind" : "$providers.orgs" },    
    { "$match" : { "providers.orgs.orgUUID" : "org001" } },    
    { "$project" : { "floorUUID" : "$providers.orgs.floors.floorUUID" } },    
    { "$unwind" : "$floorUUID" },    
    { "$project" : { "_id":0 , "floorUUID" : 1 } }
)

Answer 2

Mongo aggregation use to finding to nested documents. First unwind all providers array then use match to match providerUUID as given prov001 then used project to get all orgUUID and aggregation query as :

db.collectionName.aggregate({"$unwind":"$providers"},
                            {"$match":{"providers.providerUUID":"prov001"}},
                            {"$project":{"orgUUID":"$providers.orgs.orgUUID"}},
                            {"$unwind":"$orgUUID"},
                            {"$project":{"_id":0,"orgUUID":1}}
                           ).pretty()

this will returns all orgUUID in an array.

If you use $elemMacth then this operator having it's own limitation as

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

elemMatch query as :

 db.collectionName.find({"providers":{"$elemMatch":{"providerUUID":"prov001"}}},
                        {"providers.$.providerUUID.orgs.orgUUID":1}).pretty()

it returns whole matching providers array.

I hope you will find out "2" question query yourself, If you having any trouble with finding with "2" query I will post "2" query also. Try to yourself to find out second query answer yourself :)