1

I'm trying to retrieve a list of sub arrays of a document which meets a particular condition.

"_id" : "something",
"players" : [
    {
        "Name" : "Sunny"
        "score": 20
    },
    {
        "Name" : "John"
        "score" : 40
    },
    {
        "Name" : "Alice"
        "score" : 20
    },
    etc...
]

I wanted output of those with score = 20 like

{
    "Name" : "Sunny"
    "score": 20
},
{
    "Name" : "Alice"
    "score" : 20
}

But I tried querying with:

db.collection.find(
    { "players.score":20, "_id":"something" },
    { "players" :1 }
) 

But it gives me all the 3 sub arrays like

{
    "Name" : "Sunny"
    "score": 20
},
{
    "Name" : "John"
    "score" : 40
},
{
     "Name" : "Alice"
    "score" : 20
}

If I use projector "$" or $matchelement like:

db.collection.find({ "players.$.score":20, "_id":"something" }

It gives the very first array example

{
    "Name" : "Sunny"
    "score": 20
}

Can anyone help me out with correct query for this . thanks in advance :)

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Aditya
  • 13
  • 4

1 Answers1

1

Yes the positional $ operator will only match the first element found in a matching array condition. In order to just filter the elements you want, use aggregate:

db.collection.aggregate([
    // Uwinds the array (de-normalize)
    { "$unwind": "$players" },

    // Match just the elements you want
    { "$match": { "players.score": 20 } },

    // Push everything back into an array like it was        
    { "$group": { 
        "_id": "$_id",
        "players": { "$push": { 
            "name": "$players.name",
            "score": "$players.score"
        }} 
    }}
])

If your document has more detail and you need that back as well, see here.

For the record, the other operator you were trying other than the direct dot . notation was $elemMatch.

Community
  • 1
  • 1
Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
  • @Aditya How is copying code your own answer? And what you have posted does not produce the results you asked for in your question. "I wanted output of those with score = 20 like" and the listing is the documents in the array. Which is what $push is doing here. Leaving it unwound is not the state of the document as it was. – Neil Lunn Mar 10 '14 at 06:14
  • @Aditya Though I probably should have checked in the code that the array was being reconstructed correctly. Which it does now with an edit. The correct practice here is to accept the answer of those who help you. – Neil Lunn Mar 10 '14 at 06:26
  • i actual used the same query you provided i dint change anything .i wanted to edit the previous comment but it dint allow me . – Aditya Mar 10 '14 at 10:35
  • i even wanted to upvote ur post but i do not have enough rep to do so. – Aditya Mar 10 '14 at 10:39
  • @Aditya You **can** accept and answer. Which is the right thing to do when gained knowledge helps you. And I believe that even increases your rep score. If not only a new badge to your name. – Neil Lunn Mar 10 '14 at 10:46