How to calculate difference between values of different documents using mongo aggregation?

Question

Hi my mongo structure as below

{
"timemilliSec":1414590255,
"data":[
    {
    "x":23,
    "y":34,
    "name":"X"
    },
    {
    "x":32,
    "y":50,
    "name":"Y"
    }
    ]
},
{
"timemilliSec":1414590245,
"data":[
    {
    "x":20,
    "y":13,
    "name":"X"
    },
    {
    "x":20,
    "y":30,
    "name":"Y"
    }
    ]
}

Now I want to calculate difference of first document and second document and second to third in this way so calculation as below

diffX = ((data.x-data.x)/(data.y-data.y)) in our case ((23-20)/(34-13))
diffY = ((data.x-data.x)/(data.y-data.y)) in our case ((32-20)/(50-30))

Answer 1

Tough question in principle, but I'm going to stay with the simplified case you present of two documents and base a solution around that. The concepts should abstract, but are more difficult for expanded cases. Possible with the aggregation framework in general:

db.collection.aggregate([
    // Match the documents in a pair
    { "$match": {
        "timeMilliSec": { "$in": [ 1414590255, 1414590245 ] }
    }}

    // Trivial, just keeping an order
    { "$sort": { "timeMilliSec": -1 } },

    // Unwind the arrays
    { "$unwind": "$data" },

    // Group first and last
    { "$group": {
        "_id": "$data.name",
        "firstX": { "$first": "$data.x" },
        "lastX": { "$last": "$data.x" },
        "firstY": { "$first": "$data.y" },
        "lastY": { "$last": "$data.y" }
    }},

    // Difference on the keys
    { "$project": {
        "diff": {
            "$divide": [
                { "$subtract": [ "$firstX", "$lastX" ] },
                { "$subtract": [ "$firstY", "$lastY" ] }
            ]
        }
    }},

    // Not sure you want to take it this far
    { "$group": {
        "_id": null,
        "diffX": { 
            "$min": {
                "$cond": [
                     { "$eq": [ "$_id", "X" ] },
                     "$diff",
                     false
                 ]
            }
        },
        "diffY": { 
            "$min": {
                "$cond": [
                     { "$eq": [ "$_id", "Y" ] },
                     "$diff",
                     false
                 ]
            }
        }
    }}
])

Possibly overblown, not sure of the intent, but the output of this based on the sample would be:

{ 
    "_id" : null, 
    "diffX" : 0.14285714285714285, 
    "diffY" : 0.6 
}

Which matches the calculations.

You can adapt to your case, but the general principle is as shown.

The last "pipeline" stage there is a little "extreme" as all that is done is combine the results into a single document. Otherwise, the "X" and "Y" results are already obtained in two documents in the pipeline. Mostly by the $group operation with $first and $last operations to find the respective elements on the grouping boundary.

The subsequent operations in $project as a pipeline stage performs the required math to determine the distinct results. See the aggregation operators for more details, particularly $divide and $subtract.

Whatever you do you follow this course. Get a "start" and "end" pair on your two keys. Then perform the calculations.