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
– dnickless, Commented Nov 29, 2017 at 21:24

dnickless · Accepted Answer · 2017-11-29 23:04:17Z

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, 2017 at 23:04

answered Nov 29, 2017 at 20:56

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

bugwheels94 Over a year ago

I was just going to answer this, beat me by 10-20 seconds.

dnickless Over a year ago

Please accept my sincere apologies and let's see if our answer hits the spot to begin with...

Souvik Over a year ago

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 Over a year ago

Okay .. that was a stupid question .. in that case the query will just change to -- db.productsTest.find({"Application.DEF.Year.2018": {$exists: true}})

dnickless Over a year ago

@Souvik: Please just give us an example of the precise input document and the expected output and we will be able to help you.

|

Devon Katz · Accepted Answer · 2017-11-29 21:11:06Z

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, 2017 at 21:11

answered Nov 29, 2017 at 20:58

Devon Katz

2561 silver badge3 bronze badges

2 Comments

dnickless Over a year ago

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.

Devon Katz Over a year ago

Shoot, comparable answer got submitted while writing mine :/ Thanks @dnickless, this was a little speculation on my part, I'll edit my answer

Collectives™ on Stack Overflow

Querying on nested document in MongoDB

2 Answers 2

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related