Change Values to Keys in MongoDB aggregation query

Question

I have analytics documents in MongoDB that look like this:

{
    "_id" : ObjectId("id1"),
    "userObjectId" : "abc",
    "eventType" : "First Signup",
    "date" : ISODate("2017-09-10T20:46:42.144Z")
}

{
    "_id" : ObjectId("id2"),
    "userObjectId" : "abc",
    "eventType" : "First Launch",
    "date" : ISODate("2017-09-10T20:46:31.291Z")
}

Now I have constructed a query to group the results by date and event type:

{
    "collection": "Analytics",
    "aggregate": [
        {
            "$project": {
                "yearMonthDay": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$date"
                    }
                },
                "date": 1,
                "userObjectId": 1,
                "_id": 1,
                "eventType": 1
            }
        },
        {
            "$group": {
                "_id": { "ymd": "$yearMonthDay", "event": "$eventType" },
                "num_in_group_count": {
                    "$sum": 1
                },
                "date": { "$last": "$yearMonthDay" },
                "event": { "$last": "$eventType" }
            }
        }
    ]
}

This does generate data by date and type like so:

{
    "_id" : {
        "ymd" : "2017-09-10",
        "event" : "First Signup"
    },
    "num_in_group_count" : 2.0,
    "date" : "2017-09-10",
    "event" : "First Signup"
}

{
    "_id" : {
        "ymd" : "2017-09-10",
        "event" : "First End Onboarding"
    },
    "num_in_group_count" : 1.0,
    "date" : "2017-09-10",
    "event" : "First Launch"
}

However I would like to graph this in Redash, so would really like the data structured with the events changed to keys like so:

{
    "_id" : "2017-09-10",
    "First Signup" : 2.0,
    "First Launch" : 1.0,
    "date" : "2017-09-12"
}

How can I achieve this query?

Answer 1

you should group by date first

Then use this query to get result kindly check for null values for First Signup & Login

db.getCollection('heya').aggregate([
  {
    "$group": {
      "_id": "$date",
      "First Login": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$eventType",
                "First Launch"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "First Signup": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$eventType",
                "First Signup"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "date": {
        "$addToSet": "$date"
      }
    }
  },
  {
    "$unwind": "$date"
  }
]);