mongodb aggregate multiple nested fields

Question

I have a document structure like,

{
  "id" : 123
  "b" [
        {
         "dd"   : 21,
         "mm"   : 05,
         "yyyy" : 2015
         "prod" : "xyz"
         },
        {
         "dd"   : 22,
         "mm"   : 05,
         "yyyy" : 2015
         "prod" : "abc"
         }
       ]
 },
 {
  "id" : 512
  "b" [
        {
         "dd"   : 11,
         "mm"   : 05,
         "yyyy" : 2015
         "prod" : "xdyz"
         },
        {
         "dd"   : 22,
         "mm"   : 05,
         "yyyy" : 2015
         "prod" : "abac"
         }
       ]
 },
.....

the jsons in the list indicate date. How do I get count of dates in all documents? I want an aggregate count like

2015-05-22  count: 2
2015-05-21  count: 1
2015-05-11  count: 1

thegreenogre · Accepted Answer · 2015-04-14 12:09:24Z

3

This should work.

db.Testing.aggregate([
    {'$unwind': '$b'},
    { $project: 
        { date: 
            { $concat: [
                { 
                    "$substr": [ "$b.dd", 0, 2 ] }, "-", { "$substr": [ "$b.mm", 0, 2 ] }, "-", { "$substr": [ "$b.yyyy", 0, 4 ] }
            ]}
        }
    },
    {'$group': {'_id':'$date','Count':{'$sum': 1}} }
])

Result:

{
    "result" : [ 
        {
            "_id" : "11-5-2015",
            "Count" : 1
        }, 
        {
            "_id" : "22-5-2015",
            "Count" : 2
        }, 
        {
            "_id" : "21-5-2015",
            "Count" : 1
        }
    ],
    "ok" : 1
}

answered Apr 14, 2015 at 12:09

thegreenogre

1,57911 silver badges22 bronze badges

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

Comments

chridam · Accepted Answer · 2015-04-14 12:06:08Z

The following aggregation framework will be a close match to what you need:

db.collection.aggregate([
    {
        $unwind: "$b"
    },
    {   
        $group: {
            _id: {
                "dd" : "$b.dd",
                "mm" : "$b.mm",
                "yyyy" : "$b.yyyy"
            },
            count: { $sum: 1 }
        }
    }
]);

With the above sample collection, you will get the following aggregation result:

{
    "result" : [ 
        {
            "_id" : {
                "dd" : 22,
                "mm" : 5,
                "yyyy" : 2015
            },
            "count" : 2
        }, 
        {
            "_id" : {
                "dd" : 11,
                "mm" : 5,
                "yyyy" : 2015
            },
            "count" : 1
        }, 
        {
            "_id" : {
                "dd" : 21,
                "mm" : 5,
                "yyyy" : 2015
            },
            "count" : 1
        }
    ],
    "ok" : 1
}

If the array element field values were of string type then you could do some $concat operation on those fields in your $project pipeline stage to get the desired output

Collectives™ on Stack Overflow

mongodb aggregate multiple nested fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related