Aggregate Question

[James Moberg]

I'm looking at the MongoDB documentation demo here where a combination of city and state is used for the _id:
http://docs.mongodb.org/manual/tutorial/aggregation-zip-code-data-set/#return-average-city-population-by-state

I've been attempting to do that with a collection of logged stats (date & hit type) and the second parameter is not returned.

<CFSET StatsbyDate = Stats.aggregate(
        { "$group"={ "_id"={"HITTYPE"="$HITTYPE", "DATE"="$DATETIME.DATE"}, "total"={ "$sum"=1 } } },
        { "$project"={ "HitData"="$_id", "numberOfHits"="$total", "_id"=0 } },
        { "$sort"= { "HitData.DATE"=1 } }
    )>

But the results only return the "hit type" and do not include the date.   Am I doing something wrong or is this type of aggregation not possible with CFMongoDB?  Thanks.

array - Top 3 of 3 rows

1) [struct]
	HitData:  
		[struct]
		HITTYPE: W
	numberOfHits: 245 
2) [struct]
	HitData:  
		[struct]
		HITTYPE: P
	numberOfHits: 41 
3) [struct]
	HitData:  
		[struct]
		HITTYPE: V
	numberOfHits: 1322 

[Marc Esher]

Hey James,

Apologies for the late reply. This came in when I was out of town and it got lost in the email shuffle.

It looks to me like the problem is that you don't have date in your "$project" operator.

FWIW, my approach is to start in the mongo shell, getting the queries correct. Once I'm confident that it's constructed correctly, then I'll bring it into whatever technology I'm working with (CF, etc).

For debugging a query that you issue from CF, you can do two things:

1. in your database, turn on profiling: db.setProfilingLevel(2), which will log all queries. you can then look at them via db.system.profile.find()

2. when you issue an aggregation in cfmongodb, you can then do writeDump( result.getCommand() ); which will dump the aggregation command that was sent to mongodb.  I use that if I suspect that my original command is correct but something in cfmongodb or cf itself is mucking up the works (which is rare).

Cheers,

Marc

--
You received this message because you are subscribed to the Google Groups "CFMongoDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfmongodb+...@googlegroups.com.
To post to this group, send email to cfmo...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfmongodb.
For more options, visit https://groups.google.com/groups/opt_out.