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?

What exactly are you trying to get as a result? Could you please post a sample of your expected result document? — dnickless, Nov 29 '17 at 21:24

dnickless · Accepted Answer · 2017-11-29T23:04:17.173

3

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.

edited Nov 29 '17 at 23:04

answered Nov 29 '17 at 20:56

dnickless

10,733
1
19
34

I was just going to answer this, beat me by 10-20 seconds. – bugwheels94 Nov 29 '17 at 20:56
Please accept my sincere apologies and let's see if our answer hits the spot to begin with... – dnickless Nov 29 '17 at 20:57
I have changed the JSON structure a bit to better explain my question. Basically I am looking for query structure for a nested document as given above. As you can see in the JSON there could be multiple Year(s), Quarter(s) and even Application(s). Your query works when I know the exact values for the keys -- in my case it was Year (2018 in our example) and Quarter (Q1). However if I just want to search all Quarters for a certain Year - how do I achieve that? Thanks for your help so far .. – Souvik Nov 29 '17 at 21:24
Okay .. that was a stupid question .. in that case the query will just change to -- db.productsTest.find({"Application.DEF.Year.2018": {$exists: true}}) – Souvik Nov 29 '17 at 21:33
@Souvik: Please just give us an example of the precise input document and the expected output and we will be able to help you. – dnickless Nov 29 '17 at 21:34
@dnickless - The solution you have offered is working for all scenarios as of now. I am marking it as the correct answer. In case I come across a scenario which isn't getting handled by this solution I will put a comment and modify the question. – Souvik Nov 29 '17 at 21:38
@dnickless - Okay so here is the problem - whether i run this query - db.productsTest.find({"Application.DEF.Year.2018.Quarter.Q1": {$exists: true}}) or this one - db.productsTest.find({"Application.DEF.Year.2018": {$exists: true}}) -- it returns the same result. – Souvik Nov 29 '17 at 22:05
@Souvik: That doesn't surprise me. Please tell us *what* result you would want to get from your running your query against your sample data. – dnickless Nov 29 '17 at 22:13
@dnickless - i have modified the question and added more clarity with respect to the result I am looking for. Please let me know if you need more information. – Souvik Nov 29 '17 at 22:28
@Souvik: Check out the updated answer - this should sort you out. – dnickless Nov 30 '17 at 11:30
@dnickless - on a side note - u said in the other post that this representation is suboptimal. Do you have any suggestions with respect to how such a representation can be broken down? I wanted to keep the relations (between Products, Applications, Year, Quarter, Microservice) to a minimum and that's why tried to define them in a single schema. Do you have any other suggestions here? – Souvik Nov 30 '17 at 16:39
I would suggest to use arrays and extract all values that have a real value semantic into separate fields. So there would be e.g. a "years" array with elements like "{year: 2018, quarters: [ { quarter: 1, microservices: [...] } ]}" – dnickless Nov 30 '17 at 16:44

Devon Katz · Answer 2 · 2017-11-29T21:11:06.557

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.

edited Nov 29 '17 at 21:11

answered Nov 29 '17 at 20:58

Devon Katz

246
1
3

Although I would agree to the statement that this particular document structure looks suboptimal, from a performance point of view, flat structures are actually *not* the preferred option and deep nesting is the way to go. IIRC, this is because of the way BSON processing works. – dnickless Nov 29 '17 at 21:07
Shoot, comparable answer got submitted while writing mine :/ Thanks @dnickless, this was a little speculation on my part, I'll edit my answer – Devon Katz Nov 29 '17 at 21:08

Querying on nested document in MongoDB

2 Answers2