average values using pymongo, javascript, and aggregate

[John Harrison]

Hi All,

I'm interesting in returning some average values from mongodb/pymongo, using aggregate and a custom javascript function, as:

db.system.js.save(
{
    '_id': 'ts_secs',
    value: function( _ts ){
        var d = new Date( _ts );
        var s = d.getHours()*3600;
        s += d.getMinutes()*60;
        s += d.getSeconds();
        return s;
    }
}
);

    match={'HOST_ID':{'$in':sids},
           'BEGIN_TS':{'$gte':begin_ts,'$lt':end_ts},
           'CONSTNT_CD':user,
           'HANGUP_IND':{'$ne':1}};

    group={'_id':'id',
                     'avg_total_events':{'$avg':'$TOTAL_EVENTS'},
                     'avg_elapsed_secs':{'$avg':db.system_js.ts_secs('$END_TS')-db.system_js.ts_secs('$BEGIN_TS')}};

    pipe = [
        {'$match':match},
        {'$group':group}
    ];

    call_avgs=db.calls.aggregate(pipeline=pipe);

result:[{u'avg_total_events': 19.520588105629766, u'avg_elapsed_secs': nan, u'_id': u'id'}] # function arguments are ISODate type, e.g. ISODate("2013-07-23T12:11:56Z"), value returned is nan.

If this approach isn't supported, would I be better off using map/reduce, and/or maybe something like python/numpy?

Let me know what you think - thanks

John

[Jon Rangel]

Hi John,

You can do this entirely within the aggregation framework, using $project stages to derive a duration in seconds from the BEGIN_TS and END_TS fields in the source documents.  See below for an example in the Javascript mongo shell syntax.  The equivalent Python syntax should be more or less identical.  I've omitted the initial $match stage in the below example for brevity.

var project1 = { "TOTAL_EVENTS" : 1,
                 "ELAPSED_MILLIS" : { "$subtract" : [ "$END_TS", "$BEGIN_TS"] }
    };
var project2 = { "TOTAL_EVENTS" : 1,
                 "ELAPSED_MILLIS" : 1,
                 "ELAPSED_REM" : { "$mod" : [ "$ELAPSED_MILLIS", 1000 ] }
    };
var project3 = { "TOTAL_EVENTS" : 1,
                 "ELAPSED_MILLIS" : 1,
                 "ELAPSED_SECS" : { "$divide" : [ { "$subtract" : [ "$ELAPSED_MILLIS", "$ELAPSED_REM" ] }, 1000 ] }
    };


var group = { "_id" : 'id',
              "avg_total_events" : { "$avg" : "$TOTAL_EVENTS" },
              "avg_elapsed_secs" : { "$avg" : "$ELAPSED_SECS" }
    };


var pipe = [ { "$project" : project1 },
             { "$project" : project2 },
             { "$project" : project3 },
             { "$group" : group}
    ];


db.foo.aggregate(pipe);

Let me know if this meets your requirements.

Regards,

Jon

[John Harrison]

Hi Jon,

Your solution works great - thanks for the help!

John

[Asya Kamsky]

And if you ever need to do more date manipulation in your aggregation
framework, my blog is full of stupid tricks for just that sort of
thing...

http://www.kamsky.org/stupid-tricks-with-mongodb.html and in particular:
http://www.kamsky.org/1/category/aggregation/1.html


Asya

> --
> --
> You received this message because you are subscribed to the Google
> Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com
> See also the IRC channel -- freenode.net#mongodb
>
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.