MongoDB Group and Subtract Values from Different Documents

Question

In my application I have the following documents

{
   "timestamp": ISODate("2015-09-17T21:14:35.0Z"),
   "sensor": "gas-in",
   "value": 2.5,
},
{
   "timestamp": ISODate("2015-09-17T21:14:35.0Z"),
   "sensor": "gas-out",
   "value": 2.0,
},
{
   "timestamp": ISODate("2015-09-17T21:20:35.0Z"),
   "sensor": "gas-in",
   "value": 6.3,
},
{
   "timestamp": ISODate("2015-09-17T21:20:35.0Z"),
   "sensor": "gas-out",
   "value": 0.8,
}

...etc...

How can I return the difference (gas-in) - (gas-out) grouped per timestamp? I am trying to write a function that returns it like this:

{
   "timestamp": ISODate("2015-09-17T21:14:35.0Z"),
   "sensor": "gas-difference",
   "value": 0.5, // is calculated by 2.5 - 2.0
},
{
   "timestamp": ISODate("2015-09-17T21:20:35.0Z"),
   "sensor": "gas-difference",
   "value": 5.5, // is calculated by 6.3 - 0.8
},

...etc...

I have played around with aggregation functions and $subtract, read other SO questions but they do not seem to have the answer to my question. In the other questions they seem to know which to 2 document to subtract from each other, but in my case I don't know the number of documents and the $timestamp "column" is the matching identifier for the two documents.

Can anybody help me with solving this? Thanks!

Answer 1

What you first need is a conditional $sum based on the $cond operator in the grouping for each value. Then you can separately $subtract:

db.gas.aggregate([
    { "$group": {
        "_id": "$timestamp",
        "gas-in": { 
            "$sum": { 
                "$cond": [
                    { "$eq": [ "$sensor", "gas-in" ] },
                    "$value", 
                    0
                ]
            }
        },
        "gas-out": { 
            "$sum": { 
                "$cond": [
                    { "$eq": [ "$sensor", "gas-out"] },
                    "$value", 
                    0
                ]
            }
        },
    }},
    { "$project": {
        "gasdifference": { "$subtract": [ "$gas-in", "$gas-out" ] }
    }}
])

Which gives the results:

{ "_id" : ISODate("2015-09-17T21:20:35Z"), "gasdifference" : 5.5 }
{ "_id" : ISODate("2015-09-17T21:14:35Z"), "gasdifference" : 0.5 }

The alternate is just make the "gas-out" values negative for a single stage:

db.gas.aggregate([
    { "$group": {
        "_id": "$timestamp",
        "gasdifference": { 
            "$sum": { 
                "$cond": [
                    { "$eq": [ "$sensor", "gas-in" ] },
                    "$value", 
                    { "$subtract": [ 0, "$value" ] }
                ]
            }
        }
    }}
])

And that's going to be more efficient.

If you have more than two possible "sensor" values then you just "nest" the $cond statements:

db.gas.aggregate([
    { "$group": {
        "_id": "$timestamp",
        "gasdifference": { 
            "$sum": { 
                "$cond": [
                    { "$eq": [ "$sensor", "gas-in" ] },
                    "$value", 
                    { "$cond": [
                        { "$eq": [ "$sensor", "gas-out" ] },
                        { "$subtract": [ 0, "$value" ] },
                        0
                    ]}
                ]
            }
        }
    }}
])

Since they are "ternary" operators ( if-then-else ) then any further logic falls within the "else" condition.