Mongodb Aggregates by Date, Counts

  mongodb, question

Data type:

{'name':'tom','date':datetime.datetime(2017, 7, 1, 11, 35)}
 {'name':'tom','date':datetime.datetime(2017, 7, 1, 12, 25)}
 {'name':'tom','date':datetime.datetime(2017, 7, 3, 13, 32)}
 {'name':'bob','date':datetime.datetime(2017, 7, 1, 15, 37)}
 {'name':'bob','date':datetime.datetime(2017, 7, 2, 9, 15)}
 {'name':'sue','date':datetime.datetime(2017, 7, 1, 17, 15)}
 {'name':'sue','date':datetime.datetime(2017, 7, 2, 12, 25)}

If you want to aggregate by date and count how many records each person has every day,
The hope is this:

{'date':'2017-7-1','name':['tom':1,'bob':1,'sue':1]}
 {'date':'2017-7-2','name':['bob':1,'sue':1]}
 {'date':'2017-7-3','name':['bob':1,'sue':1]}

Confused for several days, they were grouped according to the date, but the count was not known how to do it.

db.coll.aggregate([
 {'$group':{'_id':{'$dateToString':{'format':'%Y-%m-%d','date':'$date'}}}}
 ])

In this way, the dates are grouped, but I don’t know how to count’ name’

Thank you for the answers from the two downstairs. This has solved the problem.
Sorry, the format above is wrong, it should be this one.

{'date':'2017-7-1','name':{'tom':1,'bob':1,'sue':1}}

In python, I can convert it by defining another function.
The format can be solved from the language level.

s=[{ "_id" : "2017-08-03", "name" : [ { "name" : "tom", "count" : 1 } ] },
 { "_id" : "2017-08-01", "name" : [ { "name" : "sue", "count" : 1 }, { "name" : "bob", "count" : 1 }, { "name" : "tom", "count" : 2 } ] },
 { "_id" : "2017-08-02", "name" : [ { "name" : "sue", "count" : 1 }, { "name" : "bob", "count" : 1 } ] }]
 
 def db_dict(s):
 dicts=[]
 for line in s:
 res_dict={}
 for x in line.get('name'):
 names=x.get('name')
 values=x.get('count')
 res_dict[names]=values
 res=dict(dates=line.get('_id'),count=res_dict)
 dicts.append(res)
 return dicts

The result of function execution
You get the format you want.

[{'dates': '2017-08-03', 'count': {'tom': 1}},
 {'dates': '2017-08-01', 'count': {'tom': 2, 'bob': 1, 'sue': 1}},
 {'dates': '2017-08-02', 'count': {'bob': 1, 'sue': 1}}]

This is all I can do.
I don’t know how to use the queried value as the key of an object, that is, I don’t know how to splice objects dynamically.
source data

db.getCollection('test').insert([{'name':'tom','date':'2017-7-1'},
 {'name':'tom','date':'2017-7-1'},
 {'name':'tom','date':'2017-7-3'},
 {'name':'bob','date':'2017-7-1'},
 {'name':'bob','date':'2017-7-2'},
 {'name':'sue','date':'2017-7-1'},
 {'name':'sue','date':'2017-7-2'}
 ])

SQL statement

db.getCollection('test').aggregate([
 {
 $group: {
 _id: {
 'name': '$name',
 'date': '$date'
 },
 count: { $sum: 1 }
 }
 },{
 $project: {
 '_id': 0,
 'date': '$_id.date',
 'name': {
 'name': '$_id.name',
 'count': '$count'
 }
 }
 },{
 $group: {
 _id: '$date',
 'name': {$push: '$name'}
 }
 }
 ])

Query result

/* 1 */
 {
 "_id" : "2017-7-3",
 "name" : [
 {
 "name" : "tom",
 "count" : 1.0
 }
 ]
 }
 
 /* 2 */
 {
 "_id" : "2017-7-1",
 "name" : [
 {
 "name" : "sue",
 "count" : 1.0
 },
 {
 "name" : "bob",
 "count" : 1.0
 },
 {
 "name" : "tom",
 "count" : 2.0
 }
 ]
 }
 
 /* 3 */
 {
 "_id" : "2017-7-2",
 "name" : [
 {
 "name" : "sue",
 "count" : 1.0
 },
 {
 "name" : "bob",
 "count" : 1.0
 }
 ]
 }

Related Posts: