Aggregate count of one field based values

Working with Data
1

Hi, I have collection with schema something like below

page_id | even_type

even_type - has two values 1. load, 2.click

I’m trying to aggregate it like
page | total_impression | total_clicks

I tried following, but getting same value in both columns

db.metrics.aggregate([
{
  $group: {
      _id : {
        page_id:'$page_id',
        event_type:'load'
      }, total_impressions:{$sum :1},
      _id : {
        page_id:'$page_id',
        event_type:'click'
      }, total_clicks:{$sum :1}
    }
},
{
  $project : {
      page_id:'$_id.page_id',
      total_impressions : '$total_impressions', 
      total_clicks : '$total_clicks', 
      _id : 0
    }
  }, { $out : "metrics_results" }
])

Can you please help me?

2

We could help you better if you provide real documents. We can work with just a schema like page_id|event_type but then we need to create documents in our env. rather than just cut-n-pasting what you provide. But time being in short supply we sometimes just skip over or we supply untested solution.

As a first step, you should have a single _id field in your $group. Do for event_type what you did for page_id. You will end up with documents out of the first stage like:

{ _id : { page_id : "/index" , event_type : "load" } , total : 362 }
{ _id : { page_id : "/index" , event_type : "click" } , total : 55 }

It is not exactly the format you want but it is getting there.

3

Hi, Thanks for the response! What you posted is correct, I am also getting similar result, but I want project the data in single row as i mentioned in question

example

page | total_impression | total_clicks
48704 | 1939 | 195

{ “_id” : { “page_id” : “48704”, “event_type” : “click” }, “total” : 195 }

{ “_id” : { “page_id” : “48704”, “event_type” : “load” }, “total” : 1939 }

Thanks

4

The following stage

{
	"$group" : {
		"_id" : "$_id.pid",
		"counts" : {
			"$push" : {
				"event" : "$_id.et",
				"total" : "$c"
			}
		}
	}
}

will bring you closer.

I could not use the sample 2 documents you supplied as they where in a quote block rather than a pre, code or triple back ticks. The quotes were all screwed up and I had to type the documents so I used short field names.