CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
JustAnotherDataEnthusiast
JustAnotherDataEnthusiast

Reputation: 690

Mongo db Group by, count with a condition

Using Mongodb, I want to get the count of sensor values above 100 and sensorvalues below 100 for each particular region(group by region).

I have a sensorValue property and it has 4 sub properties namely. 1)sensorValue (the values will be 100, 200 122, 80 etc) - I want to know the count of above 100 and below 100 per region. 2)Latitude 3)Longitude 4)Region (The name of the region) - I want the count with respect to this region.

With the help of stackoverflow, I wrote the below query.

getProximityIntervalRate = (req, res) => {
    console.log("entered1")
    this.model = ProximityLocation;
    const startDate = req.headers.startdate, endDate = req.headers.enddate;
    console.log(req.headers, startDate, endDate);
    // TODO: server validatoin

    this.model.aggregate([
        { $match: { 'observationTimestamp': { $gte: new Date(startDate), $lte: new Date(endDate) } } },
        {
            $project: {
                regoin: 1,
                lessthan: {
                    $cond: [{ $lt: ["$sensorValue.sensorValue", 5] }, 1, 0]
                },
                morethan: {
                    $cond: [{ $gt: ["$sensorValue.sensorValue", 5] }, 1, 0]
                }
            }
        },
        {
            $group: { _id: { regoin: "$sensorValue.regoin" }, 
            countSmaller: { $sum: "$lessThan" },
            countBigger: { $sum: "$moreThan" } uh
            }
        },

    ], (err, location) => {
        console.log('location', location);
        if (!location) { return res.sendStatus(404); }

        res.status(200).json(location);

    });

}

I am not sure how to address the subproperty "sensorValue.regoin" under the "$project" option.Please let me know if I am missing something.

Sample Document is attached

Upvotes: 1

Views: 3719

Answers (1)

Saravana
Saravana

Reputation: 12817

You can try below aggregation to get the result

db.t66.aggregate([
    {$group: {
        _id : "$sensorValue.region", 
        lessThan : {$sum : {$cond: [{$lt : [{$toInt : "$sensorValue.sensorValue"}, 50]}, 1,0]}},
        greaterThan : {$sum : {$cond: [{$gte : [{$toInt : "$sensorValue.sensorValue"}, 50]}, 1,0]}},
    }}
])

you can remove $toInt if the sensorValue is int datatype

Upvotes: 2

Related Questions