CODEWITHSUNDEEP
node.jsmongodbmongodb-queryaggregation-framework
Bhrungarajni
Bhrungarajni

Reputation: 2535

Group data by category and date with total by date

I need to do the following:

  1. Group by date, then total number of upvotes and total number of downvotes
  2. Group by Category then aggregation has to be done by total number of upvote of all category and downvote of all category by each day upvotes and down votes of individual dates.

Here is my code: 

db.collection.aggregate([{
                                $unwind: '$votes'
                            }, {
                                $match: {
                                    'category_id': array[i]
                                }
                            }, {
                                $group: {
                                    _id:'$votes.date',
                                    "category_id": {
                                        $first: "$category_id"
                                    },
                                    up_vote: {
                                        $sum: {
                                            $cond: [{
                                                '$gt': ['$votes.score', 0]
                                            }, "$votes.score", 0]
                                        }
                                    },
                                    down_vote: {
                                        $sum: {
                                            $cond: [{
                                                '$lt': ['$votes.score', 0]
                                            }, "$votes.score", 0]
                                        }
                                    }
                                }
                            }, {
                                "$group": {
                                    "_id": "$_id",
                                    "categories": {
                                        "$push": {
                                            "category_id": "$category_id",
                                            "up_vote ": "$up_vote",
                                            "down_vote": "$down_vote"
                                        }
                                    },
                                    "total_up_vote": {
                                        $sum: {
                                            $cond: [{
                                                '$lt': ['$votes.score', 0]
                                            }, "$votes.score", 0]
                                        }
                                    },
                                    "total_down_vote": {
                                        "$sum": "$down_vote"
                                    }
                                }
                            }{
                                "$unwind": "$categories"
                            },
                            {
                                "$project": {
                                    "category_id": "$categories.category_id",
                                    "down_vote": "$categories.down_vote",
                                    "down_vote_Percentage": {
                                        "$multiply": [{ "$divide": [ "$categories.down_vote", "$total_down_vote" ] },
                                            100
                                        ]
                                    },
                                     "up_vote": "$categories.up_vote",
                                    "up_vote_Percentage": {
                                        "$multiply": [{ "$divide": [ "$categories.down_vote", "$total_total_up_vote" ] },
                                            100
                                        ]
                                    }
                                }

                           }
                            ], function(err, results) {
        res.send(result)
        })

This is my database structure:

 "_id" : ObjectId("590f1ab8a45e6eb418be32cd"),
        "category_id" : "singer",
        "celebrity_id" : ObjectId("591e71884e743d8015fd1ae0"),
        "user_id" : "591e81277bd0b65c141e64be",
        "votes" : [
                {
                        "date" : "2017/4/7",
                        "score" : -1
                },
                {
                        "date" : "2017/4/19",
                        "score" : -1
                }
        ]
}
{
        "_id" : ObjectId("59204135dab356f410d1b8a6"),
        "category_id" : "actor",
        "celebrity_id" : ObjectId("591e80e47bd0b65c141e64bc"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : 1
                }
        ]
}
{
        "_id" : ObjectId("5920415cdab356f410d1b8a7"),
        "category_id" : "actor",
        "celebrity_id" : ObjectId("591e81177bd0b65c141e64bd"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : 1
                }
        ]
}

Upvotes: 1

Views: 82

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

You want something like this:

db.collection.aggregate([
  { "$unwind": "$votes" },
  { "$group": {
    "_id": {
       "date": "$votes.date",
       "category_id": "$category_id",
    },
    "upvote": {
      "$sum": {
        "$cond": [ { "$gt": [ "$votes.score", 0 ] }, 1, 0 ]
      }
    },
    "downvote": {
      "$sum": {
        "$cond": [ { "$lt": [ "$votes.score", 0 ] }, 1, 0 ]
      }
    }
  }},
  { "$group": {
    "_id": "$_id.date",
    "categories": {
      "$push": {
        "category": "$_id.category_id",
        "upvote": "$upvote",
        "downvote": "$downvote"
      }
    },
    "total_upvote": { "$sum": "$upvote" },
    "total_downvote": { "$sum": "$downvote" }
  }},
  { "$unwind": "$categories" },
  { "$project": {
    "category": "$categories.category",
    "upvote": "$categories.upvote",
    "upvote_percent": {
      "$multiply": [
        { "$divide": [ 
          "$categories.upvote", 
          { "$cond": [{ "$eq": [ "$total_upvote", 0 ]}, 1, "$total_upvote" ] }
        ]},
        100
      ]
    },
    "downvote": "$categories.downvote",
    "downvote_percent": {
      "$multiply": [
        { "$divide": [ 
          "$categories.downvote", 
          { "$cond": [{ "$eq": [ "$total_downvote", 0 ]}, 1, "$total_downvote" ] }
        ]},
        100
      ]
    }
  }}
])

Remembering that as a "pipeline", the view of the document of each stage is equal to how the document was output from the previous stage.

Source data

{
        "_id" : ObjectId("590f1ab8a45e6eb418be32cd"),
        "category_id" : "singer",
        "celebrity_id" : ObjectId("591e71884e743d8015fd1ae0"),
        "user_id" : "591e81277bd0b65c141e64be",
        "votes" : [
                {
                        "date" : "2017/4/7",
                        "score" : -1
                },
                {
                        "date" : "2017/4/19",
                        "score" : -1
                }
        ]
}
{
        "_id" : ObjectId("59204135dab356f410d1b8a6"),
        "category_id" : "actor",
        "celebrity_id" : ObjectId("591e80e47bd0b65c141e64bc"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : 1
                }
        ]
}
{
        "_id" : ObjectId("5920415cdab356f410d1b8a7"),
        "category_id" : "actor",
        "celebrity_id" : ObjectId("591e81177bd0b65c141e64bd"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : 1
                }
        ]
}
{
        "_id" : ObjectId("5923c7fdbcc8728a67bcc653"),
        "category_id" : "actor",
        "celebrity_id" : ObjectId("591e81177bd0b65c141e64bd"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : -11
                }
        ]
}
{
        "_id" : ObjectId("5923d1b9bcc8728a67bcc655"),
        "category_id" : "blip",
        "celebrity_id" : ObjectId("591e81177bd0b65c141e64bd"),
        "user_id" : "591974b64abd73701dc7c4aa",
        "votes" : [
                {
                        "date" : "2017/4/20",
                        "score" : -11
                }
        ]
}

Output

{
        "_id" : "2017/4/19",
        "category" : "singer",
        "upvote" : 0,
        "upvote_percent" : 0,
        "downvote" : 1,
        "downvote_percent" : 100
}
{
        "_id" : "2017/4/7",
        "category" : "singer",
        "upvote" : 0,
        "upvote_percent" : 0,
        "downvote" : 1,
        "downvote_percent" : 100
}
{
        "_id" : "2017/4/20",
        "category" : "blip",
        "upvote" : 0,
        "upvote_percent" : 0,
        "downvote" : 1,
        "downvote_percent" : 50
}
{
        "_id" : "2017/4/20",
        "category" : "actor",
        "upvote" : 2,
        "upvote_percent" : 100,
        "downvote" : 1,
        "downvote_percent" : 50
}

Upvotes: 2

Related Questions