Query multiple elements in nested JSON Document

Question

I have the following sample data in MongoDB:

{
    "_id" : ObjectId("54833e93ade1a1521a2a2fe8"),
    "fname" : "yumi",
    "mname" : "sakura",
    "lname" : "kirisaki",
    "consultations" : [
        {
            "medications" : [
                "paracetamol",
                "ibuprofen",
                "carbocisteine"
            ],
            "diagnosis" : [
                "sore throat",
                "fever",
                "cough"
            ],
            "date" : ISODate("2014-12-01T16:00:00Z")
        },
        {
            "medications" : [
                "paracetamol",
                "carbocisteine",
                "afrin"
            ],
            "diagnosis" : [
                "cough",
                "colds",
                "fever"
            ],
            "date" : ISODate("2014-12-11T16:00:00Z")
        }
    ]
}
{
    "_id" : ObjectId("54833e93ade1a1521a2a2fe9"),
    "fname" : "james",
    "mname" : "legaspi",
    "lname" : "reyes",
    "consultations" : [
        {
            "medications" : [
                "zanamivir",
                "ibuprofen",
                "paracetamol"
            ],
            "diagnosis" : [
                "influenza",
                "body aches",
                "headache"
            ],
            "date" : ISODate("2014-10-22T16:00:00Z")
        },
        {
            "medications" : [
                "carbocisteine",
                "albuterol",
                "ibuprofen"
            ],
            "diagnosis" : [
                "asthma",
                "cough",
                "headache"
            ],
            "date" : ISODate("2014-11-13T16:00:00Z")
        }
    ]
}

I am trying to query patients with zanamivir AND ibuprofen AND cough:

db.patient.find({
    $and:
        [
            {"consultations.medications":["zanamivir", "ibuprofen"]},
            {"consultations.diagnosis":"cough"}
        ]
}).pretty()

So, in the short sample data, I was hoping james would be returned since he is the only one with zanamivir medication.

Nothing is happening when I enter the above query in cmd. It just goes to the next line (no syntax errors, etc.)

How must I go about the query?

Answer 1

You need the use the $all operator.

db.patient.find({ 
    "consultations.medications": { "$all" : [ "zanamivir", "ibuprofen" ]},
    "consultations.diagnosis": "cough"
})

Answer 2

Pretty simple, it's just your first part of the query.

db.patient.find({
    $and:[
         {"consultations.medications":["zanamivir", "ibuprofen"]}, 
         {"consultations.diagnosis":"cough"}]})

Asking Mongodb to find consultations.medications against ["zanamivir", "ibuprofen"] is asking it to find someone whose medications are equal to ['zanamivir', 'ibuprofen'].

If you want to find people who have had zanamivir and ibuprofen medicated you need to tweak the query to this:

db.patient.find({
    $and:[
         {"consultations.medications":"zanamivir"}, 
         {"consultations.medications":"ibuprofen"}, 
         {"consultations.diagnosis":"cough"}]})

Enjoy!