MongoDB query array of subdocuments

Question

I have researched throughly before posting this question but I could not find an accurate solution. I have the below structure

stname: "SC",
dob : "1985",
education {[
            {name : Lancaster,
             year : 2013},

            {name : Manchester, 
             year : 2001, 
             grad : 2004},

            {name : Gambia, 
             year : 2001, 
             grad : 2011}
         ]}

So I want to return only documents that have grad fields. So the last two documents should be returned.

I tried the following queries but to no avail

db.applicants.find({"education" : { $elemMatch : {"grad" : {$exists : true}}}}, {"name":1, "education.grad" : 1}).pretty()

returns only the first match as shown below,the first document is empty

{
    "_id" : ObjectId("574dd5fcbda73af19e361a3f"),
    "name" : "SC",
    "education" : [
        {

        },
        {
            "grad" : "2004"
        },
        {
            "grad" : "2011"
        }
    ]
}

Also the below query gives you similar results, that is an empty document where ever grad field is not available.

db.applicants.find({"education.grad" : { $exists : true}}, {"education.grad" : {$exists : true}, "education.grad" : 1, name : 1 , dob : 1}).pretty()

I've answer a similar question here: http://stackoverflow.com/questions/37510908/mongodb-search-by-datetime-type-not-working/37511250 — felipsmartins, May 31 '16 at 16:58
@felipsmartins, I think they are quite different, he was trying to query based on a field. — fanbondi, May 31 '16 at 17:02
@felipsmartins, see edit for the output of the first query. The empty document is the main problem. Without that everything will work fine. — fanbondi, May 31 '16 at 18:28
Possible dupe of http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection. Answers for both single and multiple element cases can be found there. — JohnnyHK, May 31 '16 at 21:50
@JohnnyHK, I have been on this post all day long but I think its not the same as my problem. Can you please post your working query. — fanbondi, May 31 '16 at 22:09

Daniele Graziani · Accepted Answer · 2016-05-31T22:33:06.053

2

UPDATE ANSWER:

db.applicants.aggregate({$unwind: "$education"}, 
{$match: {"education.grad":{$exists: true}}}, 
{$project: {"education.name": 1, "education.grad": 1, "_id": 0}})

This will return two records, no empty documents.

edited May 31 '16 at 22:33

answered May 31 '16 at 18:25

Daniele Graziani

490
4
10

this will give you the same results as my query above. You will have an empty document where ever grad is not available. – fanbondi May 31 '16 at 21:41
@fanbondi that is so strange. I tried to recreate your documents and write the same queries as you and I was not getting the same results that you posted. I think that there is something missing. To be clear, I was not getting an empty document either using my query or yours. – Daniele Graziani May 31 '16 at 21:54
Hmm then this is interesting. What must be the problem then. I am using Mongo 3.2.5. Which version are you using. db.version() will give you the results. – fanbondi May 31 '16 at 22:11
I had entered a collection that was not a match to yours (the code provided at the beginning of the question has some formatting issues). See my new answer. I hope that this works! I am using 3.2.4. – Daniele Graziani May 31 '16 at 22:34

MongoDB query array of subdocuments

1 Answers1