grouped count with a column and index position mongodb

Question

This is the structure of a document I have in one monggodb collection. I wanted to understand how one can do a mongo aggregate of grouped count over key "code" and the index position in the nested json (not the priority as it can be any number but within schedules nested there can be just 5 values):

{                                                                                                                                  
        "_id" : ObjectId("5749e9fde4b0064e7362b560"),                                                                              
        "_class" : "com.weirdcompanyname.core.collectionname",                                                            
        "rfId" : 1,                                                                                                                
        "scheduleds" : [                                                                                                        
                {                                                                                                                  
                        "code" : "556e4835f1eae40bdfa2f2001f2afc76",                                                               
                        "type" : "HT",                                                                                             
                        "priority" : 0                                                                                             
                },                                                                                                                 
                {                                                                                                                  
                        "code" : "8b2ab67af4f60e42f7ea64813b5795cf",                                                               
                        "type" : "HT",                                                                                             
                        "priority" : 1                                                                                             
                },                                                                                                                 
                {                                                                                                                  
                        "code" : "ed17101eb918b4d8c7c598e4884523ea",                                                               
                        "type" : "HT",                                                                                             
                        "priority" : 2                                                                                             
                },                                                                                                                 
                {                                                                                                                  
                        "code" : "7e0ffb4db",                                                                                      
                        "type" : "QZ",                                                                                             
                        "priority" : 3                                                                                             
                },                                                                                                                 
                {                                                                                                                  
                        "code" : "1453dfa1794f39b05f0259ad04699073",                                                               
                        "type" : "HT",                                                                                             
                        "priority" : 4                                                                                             
                }                                                                                                                  
        ],                                                                                                                         
        "created" : ISODate("2016-05-28T18:57:00.878Z")                                                                            
}

The result I'm trying to find is:

code    index_position  count
556e4835f1eae40bdfa2f2001f2afc76    0   100
8b2ab67af4f60e42f7ea64813b5795cf    1   100
ed17101eb918b4d8c7c598e4884523ea    2   100
7e0ffb4db   3   100
1453dfa1794f39b05f0259ad04699073    4   100

I could get my head around unwinding the nested json in single arrays and then grouping the code over code and maybe other column, let's say priority and have the count but the problem is to get the index position.

Is this even doable on mongo, I've read around a lot of stuff about it and I figured if I have value for which I need a position then it can be doable but I don't really have a value to look for, what I'm looking for is each code and its index position in the "scheduleds" and count. This is what I could do with my limited mongo querying skills:

db.collectionname.aggregate([{'$match':{'date_key':{'$gte': yesterday_beginning, '$lte': yesterday_end}}}, {'$unwind':'$scheduleds'}, {'$group':{'_id':{'code':'$scheduleds.code','priority':'$scheduleds.priority'}, 'rfid':{'$addToSet':'$rfId'}}}, {'$project':{'_id':0, 'code':'$_id.code', 'priority':'$_id.priority', 'totalRfid':{'$size':'$rfid'}}}, { $limit : 1000 }],{ allowDiskUse:true})

Community · Accepted Answer · 2017-05-23 12:22:37Z

1

Alain1405 says here that MongoDB 3.2 supports unwinding of the array index.

Instead of passing a path the $unwind operator, you can pass an object with the field path and the field includeArrayIndex which will hold the array index.

From MongoDB official documentation:
{
  $unwind:
  {
    path: <field path>,
    includeArrayIndex: <string>,
    preserveNullAndEmptyArrays: <boolean>
  }
}

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jun 1, 2016 at 2:57

Ezequias Dinella

1,3169 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

grouped count with a column and index position mongodb

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related