CODEWITHSUNDEEP
phpmongodb
Nathan Leggatt
Nathan Leggatt

Reputation: 450

mongo find documents where subdocument matches multiple criteria

I have a collection similar to 

{ 
"_id" : ObjectId("57832cb74114065710110971"), 
"strName" : "Some Book In Library", 
"strUPN" : "123456", 
"bHardCover": "yes",
"bIsDamaged" : "yes", 
"arrWhoHasRead" : [
    {
        "nUserID" : ObjectId("577b0b8d41140640f94894a1"), 
        "strPersonName" : "John Doe", 
        "strRole" : "Author"
    },
{
        "nUserID" : ObjectId("xyz"), 
        "strPersonName" : "Jane Doe", 
        "strRole" : "Customer"
    }
]
}

I want to return all records that have bIsDamaged = yes, and bHardCover = yes AND where (arrWhoHasRead.nUserID = 577b0b8d41140640f94894a1 AND arrWhoHasRead.strRole="Author")

I tried nesting my multiple AND condition (the one in paranthesis) in an array but that didn't seem to help much. Maybe I need projection?

I'm using this in PHP

Upvotes: 0

Views: 260

Answers (1)

Bertrand Martel
Bertrand Martel

Reputation: 45382

If you want to display all record matching your requirements including all other elements of arrWhoHasRead, a find is enough :

db.device.find({"bHardCover": "yes","bIsDamaged" : "yes","arrWhoHasRead.nUserID":ObjectId("577b0b8d41140640f94894a1"),"arrWhoHasRead.strRole":"Author"});

which will give you : 

{
    "_id": ObjectId("578d7f9aca19a63da3984899"),
    "strName": "Some Book In Library",
    "strUPN": "123456",
    "bHardCover": "yes",
    "bIsDamaged": "yes",
    "arrWhoHasRead": [{
        "nUserID": ObjectId("577b0b8d41140640f94894a1"),
        "strPersonName": "John Doe",
        "strRole": "Author"
    }, {
        "nUserID": ObjectId("578d7d6bca19a63da3984897"),
        "strPersonName": "Jane Doe",
        "strRole": "Customer"
    }]
} {
    "_id": ObjectId("578d7fb0ca19a63da398489a"),
    "strName": "Some Book In Library",
    "strUPN": "123456",
    "bHardCover": "yes",
    "bIsDamaged": "yes",
    "arrWhoHasRead": [{
        "nUserID": ObjectId("577b0b8d41140640f94894a1"),
        "strPersonName": "John Doe",
        "strRole": "Author"
    }, {
        "nUserID": ObjectId("578d7d6bca19a63da3984898"),
        "strPersonName": "Jane Doe",
        "strRole": "Customer"
    }]
}

If you want to have in results only the elements of arrWhoHasRead matching ObjectId("577b0b8d41140640f94894a1"), you can do an aggregate but no need for projection unless you want to exclude some other field : 

db.device.aggregate([{
    "$unwind": "$arrWhoHasRead"
}, {
    $match: {
        "bHardCover": "yes",
        "bIsDamaged": "yes",
        "arrWhoHasRead.nUserID": ObjectId("577b0b8d41140640f94894a1"),
        "arrWhoHasRead.strRole": "Author"
    }
}])

which will give :

{
    "_id": ObjectId("578d7f9aca19a63da3984899"),
    "strName": "Some Book In Library",
    "strUPN": "123456",
    "bHardCover": "yes",
    "bIsDamaged": "yes",
    "arrWhoHasRead": {
        "nUserID": ObjectId("577b0b8d41140640f94894a1"),
        "strPersonName": "John Doe",
        "strRole": "Author"
    }
} {
    "_id": ObjectId("578d7fb0ca19a63da398489a"),
    "strName": "Some Book In Library",
    "strUPN": "123456",
    "bHardCover": "yes",
    "bIsDamaged": "yes",
    "arrWhoHasRead": {
        "nUserID": ObjectId("577b0b8d41140640f94894a1"),
        "strPersonName": "John Doe",
        "strRole": "Author"
    }
}

Note that the array has been unwind so you will only have json object in arrWhoHasRead & as much as record as there are ObjectId("577b0b8d41140640f94894a1") in arrWhoHasRead

Upvotes: 1

Related Questions