Example data

[
   { owner: '0x123124124', createdAt: '13 May 2022 at 04:20:21 UTC'},
   { owner: '0x123124124', createdAt: '13 May 2022 at 06:20:21 UTC'},
   { owner: '0x123124124', createdAt: '13 May 2022 at 03:20:21 UTC'},
   { owner: '0x123124124', createdAt: '12 May 2022 at 07:39:34 UTC'},
   { owner: '0x123124124', createdAt: '12 May 2022 at 07:39:34 UTC'},
   { owner: '0x454545627', createdAt: '11 May 2022 at 04:13:48 UTC'}
]

And I want to query return data like this

[
  { owner: '0x123124124', createdAt: '13 May 2022', count: 3},
  { owner: '0x123124124', createdAt: '13 May 2022', count: 2},
  { owner: '0x454545627', createdAt: '11 May 2022', count: 1},
]

I have rounded with this for hours. Thanks for help.

CodePudding user response：

1. $set - Set createdAt with

   1.1. $trim - Trim string from 1.1.1.

   1.1.1. $arrayElemAt - Get the first value from 1.1.1.1.

   1.1.1.1. $split - Split createdAt string with word "at".

2. $group - Group by owner and createdAt and perform count.

3. $project - Decorate output documents.

db.collection.aggregate([
  {
    $set: {
      createdAt: {
        $trim: {
          input: {
            "$arrayElemAt": [
              {
                $split: [
                  "$createdAt",
                  "at"
                ]
              },
              0
            ]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: {
        owner: "$owner",
        createdAt: "$createdAt"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      _id: 0,
      owner: "$_id.owner",
      createdAt: "$_id.createdAt",
      count: 1
    }
  }
])

Sample Mongo Playground