how to group records based on dates in mongodb

Question

How to group data by dates? I have some docs in mongo that looks something like this: yes i have added my actuall mongo doc and my code too

    {
            "_id" : ObjectId("58c0e32161ccc654160b776a"),
            "consumer_id" : ObjectId("579f03069b49a0840409df83"),
            "user_id" : "579f034c9b49a0840409df85",
            "values" : [
                    {
                            "date" : "2017/2/9",
                            "point" : 1
                    },
                    {
                            "date" : "2017/2/10",
                            "point" : -1
                    },
                    {
                            "date" : "2017/2/11",
                            "point" : -1
                    }
            ]
    }
    {
            "_id" : ObjectId("58c0e3db61ccc654160b776b"),
            "consumer_id" : ObjectId("579f03069b49a0840409df83"),
            "user_id" : "579ef6f5a15b0eac1332034e",
            "values" : [
                    {
                            "date" : "2017/2/9",
                            "point" : 1
                    },
                    {
                            "date" : "2017/2/10",
                            "point" : 1
                    },
                    {
                            "date" : "2017/2/11",
                            "point" : -1
                    }
            ]
    }

I'd like to be able to count the no of points by date my code is like this

var array = [];
    var array2 = [];
    db.coll.find({}).toArray(function(err, result) {
        result.map(function(data) {
            array.push(data.values)
        })

It might not affect the counting points by date, but I strongly recommend you change the way you store your dates; instead of storing in string format as you currently do, it would be better to use the [BSON native date object format](http://stackoverflow.com/a/3778820/174843). — Vince Bowdren, May 11 '17 at 14:23
Can you paste the actual document? I am not sure which is the child of which in this json. because you have used array syntax [ ] in document but there is no reference. — Mihir Bhende, May 11 '17 at 14:46
@mihir Bhende yes i have updated my actual mongo doc please check — Schüler, May 11 '17 at 15:00

score 1 · Accepted Answer · answered May 11 '17 at 15:18

You would use following :

db.your_collection.aggregate([
    { $unwind : '$values'},
    { $group : {
        _id : '$values.date',
        point : { $sum : '$values.point' }
        }
    }
]);

Which will give you below result :

{ "_id" : "2017/2/11", "point" : -2 }
{ "_id" : "2017/2/10", "point" : 0 }
{ "_id" : "2017/2/9", "point" : 2 }

However, its always good to have date stored in proper date format and not as a string.

FrankTheTank_12345 · Answer 2 · 2017-05-11T13:55:59.073

0

You need to use the sort function from mongodb:

sort({datefield: -1}}

in Mongodb for example

db.products.find().sort({"created_at": 1}) --- 1 for asc and -1 for desc

in nodejs for example:

collection.find().sort({datefield: -1}, function(err, cursor){...});

edited May 11 '17 at 13:55

answered May 11 '17 at 13:50

FrankTheTank_12345

560
3
12

actually i need to group data by date and find the sum of all points of each date – Schüler May 11 '17 at 13:58

score 0 · Answer 3 · answered May 11 '17 at 14:27

You need to perform a map/reduce

I assume your array of objects are stored in the data field of your documents, themselves in an items collection.

// map function which pushes the points in an array associated to the given date date
var mapDataPoints = function() {
  for (var i=0; i<this.data.length; i++) emit(this.data[i].date, this.data[i].points);
};

// reduce function, which sums the array of points for a given date
var reduceDataPoints = function(dateId, points) {
  return Array.sum(points);
};

// map-reduce operation over the collection, which takes each document and apply the map function, then run the reduce function on the result of the map operation
// result is stored in a new collection, called pointsByDate
db.items.mapReduce(
  mapDataPoints,
  reduceDataPoints,
  { out: "pointsByDate" }
)

// pointsByDate can be queried just like any collection
db.pointsByDate.find({ "_id": "2017/2/10" });

score 0 · Answer 4 · answered Mar 23 '18 at 18:07

The following code works for your problem.

db.Stack.aggregate([
  {$unwind: "$values"},
  {$group: {
    _id: "$values.date",
    sum: {$sum: "$values.point"}
    }
  }
])

This is the output for your code

/* 1 */ { "_id" : "2017/2/11", "sum" : -2.0 }

/* 2 */ { "_id" : "2017/2/10", "sum" : 0.0 }

/* 3 */ { "_id" : "2017/2/9", "sum" : 2.0 }

how to group records based on dates in mongodb

4 Answers4