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 datadb.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 } ] }