mongodbmoralis

How to make a query includes both count and data of collection


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.


Solution

    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