CODEWITHSUNDEEP
pythonmongodbpymongoaggregation-framework
lenartowski
lenartowski

Reputation: 50

Pymongo and Aggregation framework query

I try to write aggregation query in pymongo but I just can't do it myself, I tried with documentation, tried with google, tried with Stack Overflow and I just can't find the answer. Sample from my data:

{"_id": id, "site": "site A", "weekday": 1, "value": 1}
{"_id": id, "site": "site B", "weekday": 2, "value": 0}
{"_id": id, "site": "site C", "weekday": 3, "value": 1}
{"_id": id, "site": "site A", "weekday": 2, "value": 0}
{"_id": id, "site": "site B", "weekday": 3, "value": -1}
{"_id": id, "site": "site C", "weekday": 2, "value": 1}
{"_id": id, "site": "site A", "weekday": 1, "value": -1}
{"_id": id, "site": "site B", "weekday": 3, "value": 1}

And what I need is:

For single site, let's say "site A", I need list of dictionaries for every weekday (so 7 total) with count of "values" greater than 0, equal 0 and lesser than 0. All sorted by weekdays.

So my output should look like this:

{"weekday": 1, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 2, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 3, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 4, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 5, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 6, "greaterCount": x, "lesserCount": y, "zeroCount": z}
{"weekday": 7, "greaterCount": x, "lesserCount": y, "zeroCount": z}

Of course values of greaterCount, lesserCount and zeroCount will be different for different weekdays, there is x, y and z in every dictionary in my sample output because I'm lazy.

Upvotes: 1

Views: 137

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50426

What you are basically looking for here is the $cond operator. This is a "ternary" condition that evaluates to return a value from the true/false of a logical condition.

In this case each "logical" test looks at the current "value" field and determines where true to a test such as $gt whether to return a positive value to $sum or a 0 value instead:

db.collection.aggregate([ 
    { "$group": {
        "_id": "$weekday",
        "greaterCount": {
            "$sum": {
                "$cond": [
                    { "$gt": [ "$value", 0 ] },
                    1,
                    0
                ]
            }
        },
        "lesserCount": {
            "$sum": {
                "$cond": [
                    { "$lt": [ "$value", 0 ] },
                    1,
                    0
                ]
            }
        },
        "zeroCount": {
            "$sum": {
                "$cond": [
                    { "$eq": [ "$value", 0 ] },
                    1,
                    0
                ]
            }
        }

    }}
])

Which produces on the sample:

{ "_id" : 3, "greaterCount" : 2, "lesserCount" : 1, "zeroCount" : 0 }
{ "_id" : 2, "greaterCount" : 1, "lesserCount" : 0, "zeroCount" : 2 }
{ "_id" : 1, "greaterCount" : 1, "lesserCount" : 1, "zeroCount" : 0 }

Upvotes: 1

Related Questions