Query for multiple properties on the same array element

Question

db.chat.find().pretty().limit(3)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'B',
            "body" : "hiii 1"
        },
        {
            "capty" : 'A',
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'A',
            "body" : "hiii 1"
        },
        {
            "capty" : 'B',
            "body" : "hiii 23"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7664"),
    "user1" : 1,
    "user2" : 4,
    "messages" : [
        {
            "capty" : 'A',
            "body" : "hiii 0"
        },
        {
            "capty" : 'B',
            "body" : "hiii 1"
        },
        {
            "capty" : 'B',
            "body" : "hiii 24"
        }
    ]
}

Query needed: Row count where "user1" : 1, "capty" : 'B' and "body" : "hiii 1"

I tried:

db.chat.aggregate([
 { "$match": { "user1": 1,  "messages.capty": "B" , "messages.body": "hiii 1" } }
])

but this is not working as matches any messages where capty = 'B' or "messages.body": "hiii 1".

i.e desireable output. 2 (for record1 and record3)

Answer 1

You need $elemMatch here. That operator is used for "multiple criteria" from within an array element:

db.chat.find({
 "user1": 1, 
 "messages": { 
   "$elemMatch": { "capty": "B" , "body": "hiii 1" }
 }
})

And the count:

db.chat.find({
 "user1": 1, 
 "messages": { 
   "$elemMatch": { "capty": "B" , "body": "hiii 1" }
 }
}).count()

That will match the correct documents and return their count.

Actual documents returned:

{
        "_id" : ObjectId("593921425ccc8150f35e7662"),
        "user1" : 1,
        "user2" : 2,
        "messages" : [
                {
                        "capty" : "A",
                        "body" : "hiii 0"
                },
                {
                        "capty" : "B",
                        "body" : "hiii 1"
                },
                {
                        "capty" : "A",
                        "body" : "hiii 2"
                }
        ]
}
{
        "_id" : ObjectId("593921425ccc8150f35e7664"),
        "user1" : 1,
        "user2" : 4,
        "messages" : [
                {
                        "capty" : "A",
                        "body" : "hiii 0"
                },
                {
                        "capty" : "B",
                        "body" : "hiii 1"
                },
                {
                        "capty" : "B",
                        "body" : "hiii 24"
                }
        ]
}

The same "query" condition applies to aggregate $match if you really want to use it there. But for a "count", it's faster to use the "cursor".