Using Mongo to calculate sum in Aggregator pipeline-CodePudding

I have a timeseries data in mongodb and I want to calculate the sum per day between two given dates of every sensor after I have calculated the difference between the max and min reading of the day by the sensor, using the below query

db.ts_events.aggregate([
    { $match: {
            "metadata.assetCode": { $in: [
                    "h"
                ]
            },
            "timestamp": { $gte: ISODate("2022-07-01T02:39:02.000 0000"), $lte: ISODate("2022-07-01T06:30:00.000 0000")
            }
        }
    },
    {
        $project: {
            date: {
                $dateToParts: { date: "$timestamp"
                }
            },
            activeEnergy: 1,
            "metadata.meterId": 1,
        }
    },
    {
        $group: {
            _id: {
                date: {
                    year: "$date.year",
                    month: "$date.month",
                    day: "$date.day"
                },
                meter: "$metadata.meterId",
            },
            maxValue: { $max: "$activeEnergy"
            },
            minValue: { $min: "$activeEnergy"
            },
        }
    },
    {
        $addFields: {
            differnce: { $subtract: [
                    "$maxValue",
                    "$minValue"
                ]
            },
        }
    },
])

I get the following output

{
    "_id" : {
        "date" : {
            "year" : NumberInt(2022),
            "month" : NumberInt(7),
            "day" : NumberInt(1)
        },
        "meter" : "B"
    },
    "maxValue" : 1979.78,
    "minValue" : 1979.77,
    "differnce" : 0.009999999999990905
}
{
    "_id" : {
        "date" : {
            "year" : NumberInt(2022),
            "month" : NumberInt(7),
            "day" : NumberInt(1)
        },
        "meter" : "A"
    },
    "maxValue" : 7108.01,
    "minValue" : 7098.18,
    "differnce" : 9.829999999999927
}

I want to calculate the sum of both meter difference how can I do that?

Apart from this one more problem I am facing which I am putting forward in this edited version, as you can see date is in ISODate format but I will be getting a unix epoch format, I tried to tweak the query but it is not working

db.ts_events.aggregate([
    {
        $project: {
            date: {
                $dateToParts: {
                    date: "$timestamp"
                }
            },
            activeEnergy: 1,
            "metadata.meterId": 1,
            "metadata.assetCode": 1,
            "timestamp": 1,
            startDate: {
                $toDate: 1656686342000
            },
            endDate: {
                $toDate: 1656700200000
            }
        }
    },

    {
        $match: {
            "metadata.assetCode": {
                $in: [
                    "h"
                ]
            },
            "timestamp": {
                $gte: "$startDate", $lte: "$endDate"
            }
        }
    },

    {
        $group: {
            _id: {
                date: {
                    year: "$date.year",
                    month: "$date.month",
                    day: "$date.day"
                },
                meter: "$metadata.meterId",
            },
            maxValue: {
                $max: "$activeEnergy"
            },
            minValue: {
                $min: "$activeEnergy"
            },
        }
    },
    {
        $addFields: {
            differnce: {
                $subtract: [
                    "$maxValue",
                    "$minValue"
                ]
            },
        }
    },

    {
        $group: {
            _id: "$_id.date", res: {
                $push: '$$ROOT'
            }, differnceSum: {
                $sum: '$differnce'
            }
        }
    }
])

Can you help me solve the problem?

CodePudding user response：

One option is to add one more step like this (depending on your expected output format):

This step will group together your separate documents, into one document, which will allow you to sum their values together. Be careful when grouping, since now it is a one big document and a document has a size limit.

We use $$ROOT to keep the original document structure (here inside a new array)

{$group: {_id: 0, res: {$push: '$$ROOT'}, differnceSum: {$sum: $differnce'}}}