Group based on multiple fields in a single query

Question

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.

I believe your expected output is slightly wrong for month_wise. It should be 2020-01 & 2020-02 instead of 2020-04 & 2020-05. — Ashh
– Ashh, Commented Feb 7, 2020 at 7:55
I think it's not. The format is "yyyy-dd-mmThh:mm:ss+00:00". But it's not according to iso. I just made that up to demonstrate the example. — Vasu Pal
– Vasu Pal, Commented Feb 7, 2020 at 8:20

Ashh · Accepted Answer · 2020-02-07 07:51:24Z

2

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

edited Feb 7, 2020 at 7:51

answered Feb 6, 2020 at 13:44

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vasu Pal Over a year ago

Hey @Ashh, Thanks for the answer. If I want to filter the only day_wise for a specific time period lets say the past 5 days in day_wise, how can I do this?

Ashh Over a year ago

@VasuPal Please ask a new question if you have further requirements as this answer has already got lengthy. You can follow these answer to filter arrays with date. One ,Two

Wernfried Domscheit · Accepted Answer · 2020-02-07 13:56:45Z

2

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

edited Feb 7, 2020 at 13:56

answered Feb 6, 2020 at 13:43

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

3 Comments

Vasu Pal Over a year ago

No not getting the result which i'm expecting.

Vasu Pal Over a year ago

check this link where I have multiple tags. Check this mongoplayground.net/p/c67ZVF9JkWA

Wernfried Domscheit Over a year ago

Correct, I try to find a solution based on {$facet} and without {$map} - finally I found one, please see the updated answer.

Collectives™ on Stack Overflow

Group based on multiple fields in a single query

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related