CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Vasu Pal
Vasu Pal

Reputation: 43

Group based on multiple fields in a single query

I have the following document structure

[

    {
      "tag": "abc",
      "created_at": 2020-02-05T14:20:52.907+00:00
      "likes": 12,
      "comments": 3
    },
    {
      "tag": "abc",
      "created_at": 2020-02-04T14:20:52.907+00:00
      "likes": 10,
      "comments": 1
     }
    {
      "tag": "abc",
      "created_at": 2020-01-04T14:21:52.907+00:00
      "likes": 12,
      "comments": 3
    },
    {
      "tag": "abc",
      "created_at": 2020-01-04T14:22:52.907+00:00
      "likes": 2,
      "comments": 1
     }
]

First, I want to group my documents based on tag value and then on created_at field to get something like this.

[
    {

        "tag_name": "abc",
        "day_wise": [
            {
                "dayMonthYear": "2020-01-04",
                "comments": 4,
                "likes": 14
            },
            {
                "dayMonthYear": "2020-02-04",
                "comments": 1,
                "likes": 10
            },
            {
                "dayMonthYear": "2020-02-05",
                "comments": 3,
                "likes": 12
            },
        ],
        "month_wise": [
            {
                "monthYear": "2020-04",
                "comments": 5,
                "likes": 24
            },
            {
                "monthYear": "2020-05",
                "comments": 3,
                "likes": 12
            },
        ],
    }
]

There can be multiple tags so need to group them accordingly. Can anyone please suggest what aggregation query can be applied to achieve this result.

Upvotes: 1

Views: 140

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Try this one:

db.col.aggregate([
   {
      $facet:
         {
            day_wise: [
               {
                  $group: {
                     _id: {
                        tag: "$tag",
                        day: { $dateFromParts: { year: { $year: "$created_at" }, month: { $month: "$created_at" }, day: { $dayOfMonth: "$created_at" } } }
                     },
                     likes: { $sum: "$likes" },
                     comments: { $sum: "$comments" }
                  }
               },
               {
                  $project: {
                     _id: "$_id.tag",
                     likes: 1,
                     comments: 1,
                     dayMonthYear: { $dateToString: { date: "$_id.day", format: "%Y-%m-%d" } },
                     day: "$_id.day"
                  }
               }
            ],
            month_wise: [
               {
                  $group: {
                     _id: {
                        tag: "$tag",
                        month: { $dateFromParts: { year: { $year: "$created_at" }, month: { $month: "$created_at" } } }
                     },
                     likes: { $sum: "$likes" },
                     comments: { $sum: "$comments" }
                  }
               },
               {
                  $project: {
                     _id: "$_id.tag",
                     likes: 1,
                     comments: 1,
                     monthYear: { $dateToString: { date: "$_id.month", format: "%Y-%m" } },
                     month: "$_id.month"

                  }
               }
            ],
            tags: [{ $group: { _id: "$tag" } }]
         }
   },
   { $unwind: "$tags" },
   {
      $project: {
         _id: "$tags._id",
         day_wise: {
            $filter: {
               input: "$day_wise",
               as: "item",
               cond: { $eq: ["$tags._id", "$$item._id"] }
                     // here you can add additional conditions. e.g. 
                     // { $gt: ["$day", { $subtract: ["$$NOW", 1000 * 60 * 60 * 5] }] } }  
            }
         },
         month_wise: {
            $filter: {
               input: "$month_wise",
               as: "item",
               cond: { $eq: ["$tags._id", "$$item._id"] }
            }
         }
      }
   },
   {
      $project: {
         _id: 1,
         day_wise: { likes: 1, comments: 1, dayMonthYear: 1 },
         month_wise: { likes: 1, comments: 1, monthYear: 1 },
      }
   }
])

Mongo playground

Upvotes: 2

Ashh
Ashh

Reputation: 46461

You can use below aggregation

db.collection.aggregate([
  { "$group": {
    "_id": "$tag",
    "data": {
      "$push": {
        "comments": "$comments",
        "likes": "$likes",
        "monthWise": { "$dateToString": { "date": "$created_at", "format": "%m-%Y" } },
        "dateWise": { "$dateToString": { "date": "$created_at", "format": "%d-%m-%Y" } }
      }
    }
  }},
  { "$project": {
    "dateWise": {
      "$map": {
        "input": { "$setUnion": ["$data.dateWise"] },
        "as": "m",
        "in": {
          "fieldA": "$$m",
          "count": {
            "$filter": {
              "input": "$data",
              "as": "d",
              "cond": { "$eq": ["$$d.dateWise", "$$m"] }
            }
          }
        }
      }
    },
    "monthWise": {
      "$map": {
        "input": { "$setUnion": ["$data.monthWise"] },
        "as": "m",
        "in": {
          "fieldA": "$$m",
          "count": {
            "$filter": {
              "input": "$data",
              "as": "d",
              "cond": { "$eq": ["$$d.monthWise", "$$m"] }
            }
          }
        }
      }
    }
  }},
  { "$project": {
    "dateWise": {
      "$map": {
        "input": "$dateWise",
        "in": {
          "dateWise": "$$this.fieldA",
          "likes": { "$sum": "$$this.count.likes" },
          "comments": { "$sum": "$$this.count.comments" }
        }
      }
    },
    "monthWise": {
      "$map": {
        "input": "$monthWise",
        "in": { "monthWise": "$$this.fieldA",
          "likes": { "$sum": "$$this.count.likes" },
          "comments": { "$sum": "$$this.count.comments" }
        }
      }
    }
  }}
])

MongoPlayground

Upvotes: 2

Related Questions