An example tutorial for aggregating queries by day in mongodb

  • 2020-12-26 06:00:26
  • OfStack

preface

Recently in writing the project meet a problem, use mongodb recorded the execution result of the use case, but at the time of use is date format, now there is a demand, on a statistical 1 under the successful cases and failed cases every day, said to the statistics, affirmation is used to aggregate queries, but if time for group basis in date format, so is not grouping, because in recorded cases of time almost impossible at the same time, consult the related documents under 1 today, can use mongodb $dateToString command to complete this requirement

Source of problem

Suppose we take the following data


/* 1 */
{
 "_id" : ObjectId("5d24c09651a456efbc231669"),
 "time" : ISODate("2019-07-08T10:12:35.125Z"),
 "result" : "Pass"
}

/* 2 */
{
 "_id" : ObjectId("5d24c09e51a456efbc23166a"),
 "time" : ISODate("2019-07-08T10:12:36.125Z"),
 "result" : "Pass"
}

...
...

/* 10 */
{
 "_id" : ObjectId("5d24c0d851a456efbc231672"),
 "time" : ISODate("2019-07-06T10:10:52.125Z"),
 "result" : "Pass"
}

/* 11 */
{
 "_id" : ObjectId("5d24c0e751a456efbc231673"),
 "time" : ISODate("2019-07-06T10:10:52.125Z"),
 "result" : "Fail"
}

My expectation is that

[

{'_id': '2019-07-06', 'Pass': 1}
{'_id': '2019-07-06', 'Fail': 2}
{'_id': '2019-07-07', 'Pass': 2}
{'_id': '2019-07-07', 'Fail': 1}
{'_id': '2019-07-08', 'Pass': 2}
{'_id': '2019-07-08', 'Fail': 3}

]

If you group by $time in the same way as before, since each time is different, such an aggregation is equivalent to no aggregation


#coding:utf-8

from pymongo import MongoClient

client = MongoClient(host=['%s:%s'%("127.0.0.1",27017)])
G_mongo = client['test']

pipeline = [
  {'$group': {'_id': '$time', 'count': {'$sum': 1}}},
 ]
for i in G_mongo['test'].aggregate(pipeline):
 print(i)

The result that you get

[

{'_id': datetime.datetime(2019, 7, 6, 10, 10, 32, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 7, 10, 10, 32, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 8, 10, 11, 22, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 6, 10, 10, 52, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 8, 10, 11, 32, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 8, 10, 12, 32, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 7, 10, 11, 22, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 8, 10, 12, 36, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 8, 10, 12, 35, 125000), 'count': 1}
{'_id': datetime.datetime(2019, 7, 7, 10, 10, 22, 125000), 'count': 1}

]

As you can see, because of the time on $time, neither one is the same as the other, so each statistic would be 1 if you grouped objects in $time.

Problem solving

There is a $dateToString instruction at grouping that converts date-formatted values into strings. For example, since the requirement is in days, I turned it into a string
%Y-%m-%d string format, specific $grouop below


{'$group': {'_id': {"$dateToString":{'format':'%Y-%m-%d','date':'$time'}}, 'count': {'$sum': 1}}}

$dateToString documentation can access https: / / docs mongodb. com manual/reference operator/aggregation/dateToString/view, one simple introduction


{ $dateToString: {
  date: <dateExpression>,
  format: <formatString>,
  timezone: <tzExpression>,
  onNull: <expression>
} }

It requires four parameters, only the date parameter is required, specifying the data source, format is the converted format, timezone is the time zone, and onNull is the value returned if the date value does not exist.


#coding:utf-8

from pymongo import MongoClient

client = MongoClient(host=['%s:%s'%("127.0.0.1",27017)])
G_mongo = client['test']


pipeline = [
    # {'$group': {'_id': '$time', 'count': {'$sum': 1}}},
    {'$group': {'_id': {"$dateToString":{'format':'%Y-%m-%d','date':'$time'}}, 'count': {'$sum': 1}}}
  ]
for i in G_mongo['test'].aggregate(pipeline):
  print(i)

The result of the above code execution is as follows

[

{'_id': '2019-07-06', 'count': 2}
{'_id': '2019-07-07', 'count': 3}
{'_id': '2019-07-08', 'count': 5}

]

This looks good, but it's still one point short of my goal, because it hasn't been grouped by use case execution and sorted by day


#coding:utf-8

from pymongo import MongoClient

client = MongoClient(host=['%s:%s'%("127.0.0.1",27017)])
G_mongo = client['test']


pipeline = [
    # {'$group': {'_id': '$time', 'count': {'$sum': 1}}},
    {'$group': {'_id': {'date':{"$dateToString":{'format':'%Y-%m-%d','date':'$time'}},'result':'$result'}, 'count': {'$sum': 1}}},
    {'$sort':{"_id.date":-1}}
  ]
for i in G_mongo['test'].aggregate(pipeline):
  print(i)

The results are as follows

[

{'_id': {'date': '2019-07-08', 'result': 'Fail'}, 'count': 3}
{'_id': {'date': '2019-07-08', 'result': 'Pass'}, 'count': 2}
{'_id': {'date': '2019-07-07', 'result': 'Pass'}, 'count': 2}
{'_id': {'date': '2019-07-07', 'result': 'Fail'}, 'count': 1}
{'_id': {'date': '2019-07-06', 'result': 'Fail'}, 'count': 1}
{'_id': {'date': '2019-07-06', 'result': 'Pass'}, 'count': 2}

]

To view the documentation, you can use the $dayOfMonth directive in addition to the $dateToString directive


pipeline = [
    {'$group': {'_id': {'date':{"$dayOfMonth":{'date':'$time'}},'result':'$result'}, 'count': {'$sum': 1}}},
    {'$sort':{"_id.date":-1}},
  ]

But this directive only applies to January alone, if two months will have intersection, such as July 6 and June 6 will converge to 1
So this is the result up here

[

{'_id': {'date': 8, 'result': 'Fail'}, 'count': 3}
{'_id': {'date': 8, 'result': 'Pass'}, 'count': 2}
{'_id': {'date': 7, 'result': 'Pass'}, 'count': 2}
{'_id': {'date': 7, 'result': 'Fail'}, 'count': 1}
{'_id': {'date': 6, 'result': 'Pass'}, 'count': 2}
{'_id': {'date': 6, 'result': 'Fail'}, 'count': 1}

]

Therefore, we need to flexibly use various instructions according to the requirements.

conclusion


Related articles: