Querying on nested document in MongoDB

Question

I have a complex document structure like below -

{
    "Application": {
        "DEF": {
            "Year": {
                "2018": {
                    "Quarter": {
                        "Q1": {
                            "Microservice": [ "A", "B" ]
                        },
                        "Q2": {
                            "Microservice": [ "C", "D" ]
                        },
                        "Q3": {
                            "Microservice": [ "E" ]
                        },
                        "Q4": {
                            "Microservice": [ "F", "G" ]
                        }
                    }
                },
                "2019": {
                    "Quarter": {
                        "Q1": {
                            "Microservice": [ "A", "C" ]
                        },
                        "Q2": {
                            "Microservice": [ "D" ]
                        },
                        "Q3": {
                            "Microservice": [ "E", "F" ]
                        },
                        "Q4": {
                            "Microservice": [ "G" ]
                        }
                    }
                }
            }
        }
    },
    "Product Name": "XYZ"
}

I am trying to query all the records where Application is DEF, Year is 2018 and all Quarters. I have tried the DOT(.) notation like below --

db.productsTest.find({"Application.DEF.Year.2018": {$exists: true}})

The above returns results for all Years (2018 and 2019) instead of just returning the Year, Quarter and Microservice combination for only 2018. This could also be because of the JSON structure and I can't filter by Year (since they are nested). Basically I am looking for the query which returns this --

{
    "Application": {
        "DEF": {
            "Year": {
                "2018": {
                    "Quarter": {
                        "Q1": {
                            "Microservice": [ "A", "B" ]
                        },
                        "Q2": {
                            "Microservice": [ "C", "D" ]
                        },
                        "Q3": {
                            "Microservice": [ "E" ]
                        },
                        "Q4": {
                            "Microservice": [ "F", "G" ]
                        }
                    }
                }
            }
        }
    },
    "Product Name": "XYZ"
}

Is that result even possible given my JSON structure?

Answer 1

The following query gets the job done:

db.productsTest.find({
    "Application.DEF.Year.2018": { $exists: true } // exclude documents from the result that do not contain the subdocument that we are interested in
}, {
    "_id": 0, // we do not want the _id field in the result document
    "Product Name" : 1, // but the "Product Name" should be included
    "Application.DEF.Year.2018": 1 // and so should be the subdocument we are interested in
})

Basically, that's just a standard query with a projection.

$exists is an element operator which checks if a property exists or not.

Answer 2

You can use the $exists operator to find documents that contain a specified field! This works for me using the test data you provided:

db.productsTest.findOne({"Application.DEF.Year.2018.Quarter.Q1":{$exists:true}})

and returns the test document you provided.

As a side note: Unless you have a good reason to use this deeply of a nested structure flattening your documents can help readability.