CODEWITHSUNDEEP
mysqlmongodbmongodb-queryaggregation-framework
PRUDHVI RAJ
PRUDHVI RAJ

Reputation: 41

MongoDB - Count documents based on if a field has a particular value in group stage

I Wrote an Mysql query like this :

SELECT account_id,date(date_posted) as date_posted,sum(if(message_format = 'e',1,0)) as email_count,sum(if(message_format = 't',1,0)) as 
sms_count,sum(if(message_format = 'p',1,0)) as push_count,sum(if(message_format = 's',1,0)) as slack_count
FROM `track_notifications` GROUP BY date(date_posted), account_id ORDER BY account_id

The MySQL result is shown in the below image :

enter image description here

I want to get the same as MySQL result in MongoDB, I am new to MongoDB. How to write this query in MongoDB

I tried like this :

db.Datasets.aggregate([{$project:{_id:0,account_id:1,date_posted:1,message_format:{$ifNull:["$message_format",0]}}},{$sort:{"account_id":1}}])

Sample Docs :

_id:ObjectId(“5e8851dc53a883900d9b8e4b”)
account_id:52519
date_posted:”2018-11-19T15:38:52.000Z”
message_format:”t”

_id:ObjectId(“5e8851dc53a883900d9b8e4c”)
account_id:52519
date_posted:”2018-11-19T15:43:01.000Z”
message_format:”e”

_id:ObjectId(“5e8851dc53a883900d9b8e4d”)
account_id:52519
date_posted:”2018-11-19T15:38:51.000Z”
message_format:”e”

_id:ObjectId(“5e8851dc53a883900d9b8e4d”)
account_id:52519
date_posted:”2018-11-19T15:56:22.000Z”
message_format:”s”

_id:ObjectId(“5e8851dc53a883900d9b8e4e”)
account_id:52519
date_posted:”2018-11-19T15:56:22.000Z”
message_format:”p”

After a long research & try, I'm stuck in here sum(if(message_format = 't',1,0)), Please help.

Upvotes: 0

Views: 1121

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Try below aggregation query :

Steps :

  1. You would group on account_id & converted date_posted & have a conditional check on message_format field & pass 1 or 0 to $sum based on field value, if field is null then 0 will be passed, So while grouping docs, if 10 docs have message_format == e then email_count will be 10 & rest others will be 0.
  2. Then you can add account_id & date_posted fields extracted from $_id.
  3. Remove unnecessary _id field using project.
  4. Sort on account_id to order result docs in ascending order.

Query :

db.track_notifications.aggregate([
      {
        $group: {
          _id: {
            account_id: "$account_id",
            date_posted: {
              $dateToString: { format: "%Y-%m-%d", date: "$date_posted" },
            },
          },
          email_count: {
            $sum: { $cond: [{ $eq: ["$message_format", "e"] }, 1, 0] },
          },
          sms_count: { $sum: { $cond: [{ $eq: ["$message_format", "t"] }, 1, 0] } },
          push_count: {
            $sum: { $cond: [{ $eq: ["$message_format", "p"] }, 1, 0] },
          },
          slack_count: {
            $sum: { $cond: [{ $eq: ["$message_format", "s"] }, 1, 0] },
          },
        },
      },
      {
        $addFields: {
          account_id: "$_id.account_id",
          date_posted: "$_id.date_posted",
        },
      },
      { $project: { _id: 0 } },
      { $sort: { account_id: 1 } },
    ]);

Test : MongoDB-Playground

Ref : aggregation-pipeline-operators

Upvotes: 2

Related Questions