Rakshitha T.R Rakshitha T.R - 1 year ago 37
Node.js Question

Group data by category and date with total by date

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 please check and do needfull needful:

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
}
]
}

Answer Source

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
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download