groups by name and provider, then group by month and then get the average of month wise in mongoose

user_9413722 0
Viewed 43

I want to groups by name and provider, then group by month, and then get the average of month-wise in mongoose the expected result, average group by name and month. Thanks

my collection in MongoDB looks like below:

{
    "_id" : ObjectId("614d713c28af063f4cffbb6f"),
    "name" : "Chibi Doge",
    "id" : "0x7c8e01e8df38f001bcefb3b8901ad5d64374dbdf",
    "logo" : "https://img.rarible.com/prod/image/upload/t_avatar_big/prod-collections/0x7c8e01e8df38f001bcefb3b8901ad5d64374dbdf/avatar/Qmdp79f2nP8HRYwwQiEDuRcy6F9ysW4WPdG5QiSvpd57Hu",
    "provider" : "rarible",
    "totalSalesUsd" : 27436.1841495685,
    "totalNFTs" : 59,
    "createdAt" : ISODate("2021-09-24T06:33:32.708Z"),
    "updatedAt" : ISODate("2021-09-24T06:33:32.537Z"),
    "__v" : 0
}

/* 2 */
{
    "_id" : ObjectId("614d713d28af063f4cffbb73"),
    "name" : "",
    "id" : "0xa7206d878c5c3871826dfdb42191c49b1d11f466",
    "logo" : "https://img.rarible.com/prod/image/upload/t_avatar_big/prod-collections/0xa7206d878c5c3871826dfdb42191c49b1d11f466/avatar/QmYCxLp2puXnnarXHyTcY9onjMqrTAUNQskpirL5VPFF6B",
    "provider" : "rarible",
    "totalSalesUsd" : 26176.2678881893,
    "totalNFTs" : 7,
    "createdAt" : ISODate("2021-09-24T06:33:33.228Z"),
    "updatedAt" : ISODate("2021-09-24T06:33:33.057Z"),
    "__v" : 0
}

I write a query which results in the below result:

[
    {
        "_id": {
            "provider": "superrare",
            "name": "batsoupyum2"
        },
        "data": [
            {
                "provider": "superrare",
                "name": "batsoupyum2",
                "totalSalesUsd" : 26176.2678881893,
                "createdAt": "2021-09-24T06:23:32.802Z"
            },
            {
                "provider": "superrare",
                "name": "batsoupyum2",
                "totalSalesUsd" : 26176.2678881893,
                "createdAt": "2021-08-24T06:33:32.708Z"
            }
        ]
    }
    ]

and the query is

collection.aggregate([
    {
      $group: {
        _id: {
          provider: "$provider",
          name: "$name",
          // month: { $month: "$createdAt" },
          // avgQuantity: { $avg: "$totalSalesUsd" }
        },
        data: {
        $push: {
          provider: "$provider",
             name: "$name",
             totalSalesUsd: "$totalSalesUsd",
             createdAt: "$createdAt"
            // average: "$_id.average"
          },
         },
      },
    },
])

How can I do this using the mongoose.js framework?

mongoose node.js mongodb
1 Answers

You're quite close, what you want to do is add another $group stage, you need to start by $grouping on provider AND time, and only then you group again on just the provider, like so:

db.collection.aggregate([
  {
    $group: {
      _id: {
        provider: "$provider",
        name: "$name",
        month: {
          $month: "$createdAt"
        },
        year: {
          $year: "$createdAt"
        }
      },
      avgQuantity: {
        $avg: "$totalSalesUsd"
      }
    }
  },
  {
    $group: {
      _id: {
        provider: "$_id.provider",
        name: "$_id.name"
      },
      data: {
        $push: {
          provider: "$_id.provider",
          name: "$_id.name",
          avgQuantity: "$avgQuantity",
          month: "$_id.month",
          year: "$_id.year"
        }
      }
    }
  }
])

Mongo Playground

user_107703700
Related