mongodb select item from array, group them and count total

Question

When I write like this

db.Profiles.find({ userId:4790, "p2l.listId":31618 }, { "p2l.$": 1 } )

I get the desired result

 ...
 {
    "_id" : ObjectId("56052d35df3e5e564f770ed0"),
    "p2l" : [ 
        {
            "listId" : 31618,
            "status" : 131
        }
    ] }

{
    "_id" : ObjectId("56052d25df3e5e564f770ecd"),
    "p2l" : [ 
        {
            "listId" : 31618,
            "status" : 25
        }
    ] }

 {
    "_id" : ObjectId("56052d1adf3e5e564f770eca"),
    "p2l" : [ 
        {
            "listId" : 31618,
            "status" : 25
        }
    ] }

 {
    "_id" : ObjectId("5603c882bd1f3d3668ba7352"),
    "p2l" : [ 
        {
            "listId" : 31618,
            "status" : 24
        }
    ] }

, but I have to group it by status

How I can do it?

I try as

 db.Profiles.aggregate( {$match:{  userId:4790, "p2l.listId":31618}},

{$project:{"p2l.listId":"$p2l.listId", "p2l.status":"$p2l.status"}} )

but in rezult, I have values, that do not meet the parameters of the request. As a result, there listId = 31617

{
    "result" : [ 
        ...
        {
            "_id" : ObjectId("56052d25df3e5e564f770ecd"),
            "p2l" : [ 
                {
                    "listId" : [ 
                        31618
                    ],
                    "status" : [ 
                        25
                    ]
                }
            ]
        }, 
        {
            "_id" : ObjectId("56052d1adf3e5e564f770eca"),
            "p2l" : [ 
                {
                    "listId" : [ 
                        31618, 
                        31617
                    ],
                    "status" : [ 
                        25, 
                        25
                    ]
                }, 
                {
                    "listId" : [ 
                        31618, 
                        31617
                    ],
                    "status" : [ 
                        25, 
                        25
                    ]
                }
            ]
        }, 
        {
            "_id" : ObjectId("5603c882bd1f3d3668ba7352"),
            "p2l" : [ 
                {
                    "listId" : [ 
                        31618
                    ],
                    "status" : [ 
                        24
                    ]
                }
            ]
        }
    ],
    "ok" : 1.0000000000000000,
    "$gleStats" : {
        "lastOpTime" : Timestamp(0, 0),
        "electionId" : ObjectId("5606384b5e0803423d340427")
    }
}

possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection). Which means various examples showing `$unwind` and how you need to "filter" results "after" the `$unwind` is complete to reduce array matched. Please stop editing and start reading what you should have found in search results in the first place. — Blakes Seven, Sep 28 '15 at 14:06
It should also help you with that great big banner you should be seeing now that has a "big blue button" which says "this helped me" (sic). So please "press it" and close this as a duplicate as intended. StackOverflow is a Question and Answer resource that does not need a "couple of thousand" answers that are all essentially the same thing. Close the question and the link stays intact to both for those who might search with your terms as well. — Blakes Seven, Sep 28 '15 at 14:28

Veronika Kostenko · Answer 1 · 2015-09-28T14:48:13.867

0

I found a solution, here's an example

 db.Profiles.aggregate(
  { $match : {
     "p2l.listId": 31618
  }},
  { $unwind : "$p2l" },
  { $match : {
     "p2l.listId": 31618
  }},
  {$project:{"p2l":"$p2l"}},
  {$group:{_id : { "p2l.status": "$p2l.status"},
  "count": { $sum: 1 }}}
)

edited Sep 28 '15 at 14:48

answered Sep 28 '15 at 14:27

Veronika Kostenko

265
1
2
13

Intrinsically incorrect as an approach. As you still "need" to `$match` "before" you `$unwind` and array, otherwise you are process a whole bunch of documents that do not match the conditions you want. A `$match` "after" the `$unwind` is meant to "filter" the undesired results from the array content "after" the documents that have possible matches are identified. – Blakes Seven Sep 28 '15 at 14:36
But it works, and returns the result I needed. If you know how else I can get the result I needed, write. – Veronika Kostenko Sep 28 '15 at 14:41

mongodb select item from array, group them and count total

1 Answers1