Mongodb sort and group by-CodePudding

I'm not sure that my question is correct, but it seems so:

I have a set of rows in my Mongodb, like:

[{'_id': '5b4c9aa7ddc752c1f5844315',
  'ccode': 'RU',
  'date': '2018-07-16T00:00:00.000Z',
  'rates': {'reg_emails_confirmed': 4,
            'registered': 1,
            'regs_age1': 1,
            'regs_male': 1}},
 {'_id': '5b4cad0dddc752c1f5844322',
  'ccode': 'US',
  'date': '2018-07-16T00:00:00.000Z',
  'rates': {'reg_emails_confirmed': 4,
            'registered': 2,
            'regs_age1': 2,
            'regs_male': 2}},
 {'_id': '5bd88204af4c814883a414b2',
  'ccode': 'US',
  'date': '2018-10-30T00:00:00.000Z',
  'rates': {'reg_emails_confirmed': 2,
            'registered': 1,
            'regs_age1': 1,
            'regs_male': 1}},
 {'_id': '5bd88204af4c814883a414b3',
  'ccode': 'RU',
  'date': '2018-10-30T00:00:00.000Z',
  'rates': {'reg_emails_confirmed': 2,
            'registered': 1,
            'regs_age1': 1,
            'regs_male': 1}}]

And I want to sort them by date and combine because for the same date there are multiple rows from different countries. So the result should look something like ...

[{'2018-07-16T00:00:00.000Z': [{'_id': '5b4c9aa7ddc752c1f5844315',
                                'ccode': 'RU',
                                'date': '2018-07-16T00:00:00.000Z',
                                'rates': {'reg_emails_confirmed': 4,
                                          'registered': 1,
                                          'regs_age1': 1,
                                          'regs_male': 1}},
                               {'_id': '5b4cad0dddc752c1f5844322',
                                'ccode': 'US',
                                'date': '2018-07-16T00:00:00.000Z',
                                'rates': {'reg_emails_confirmed': 4,
                                          'registered': 2,
                                          'regs_age1': 2,
                                          'regs_male': 2}}]},
 {'2018-10-30T00:00:00.000Z': [{'_id': '5bd88204af4c814883a414b2',
                                'ccode': 'US',
                                'date': '2018-10-30T00:00:00.000Z',
                                'rates': {'reg_emails_confirmed': 2,
                                          'registered': 1,
                                          'regs_age1': 1,
                                          'regs_male': 1}},
                               {'_id': '5bd88204af4c814883a414b3',
                                'ccode': 'RU',
                                'date': '2018-10-30T00:00:00.000Z',
                                'rates': {'reg_emails_confirmed': 2,
                                          'registered': 1,
                                          'regs_age1': 1,
                                          'regs_male': 1}}]}]

I tried:

    db.getCollection('daily_stats').aggregate([
        {'$match': some_condition},
        {'$group': {'ccode': 1}},  # ccode or date?
        {'$sort': {"date": 1}},
    ])

But got an error The field * must be an accumulator object

I googled the error, it's pretty clear, but not seems that related to my case. I don't need any sum, avg, etc functions

CodePudding user response：

Query

sort by date (asceding here, if you need descending put -1)
group by date and collect the ROOT documents
replace the root so you have the date as key

*this assumes you have dates on strings, which is bad idea, if you convert them to date objects, you can still use the query but add "k":{"$dateToString" : {"date" :"$_id"}}

Test code here

aggregate(
[{"$sort":{"date":1}},
 {"$group":{"_id":"$date", "docs":{"$push":"$$ROOT"}}},
 {"$replaceRoot":
  {"newRoot":{"$arrayToObject":[[{"k":"$_id", "v":"$docs"}]]}}}])

CodePudding user response：

When using $group, you need an _id

From the docs

{
  $group:
    {
      _id: <expression>, // Group By Expression
      <field1>: { <accumulator1> : <expression1> },
      ...
    }
 }

In your case...

   db.getCollection('daily_stats').aggregate([
        {'$match': some_condition},
        {'$group': {
               '_id': "$ccode", 
               'rates': { $addToSet: '$rates' },
               'date': { $first: '$date' }
        }},  
        {'$sort': {"date": 1}},
        {'$project: { "_id": 0, "country": "$_id", "rates": 1, "date": 1 }}
    ])

Playground: https://mongoplayground.net/p/B31XLS9p-6W