Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Using Mongo to calculate sum in Aggregator pipeline

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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'}}}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading