Max And Group By In Mongodb

First of all we are just migrating from SQL Server to Mongodb. I have a collection containing fields TFN, Impressions. I need to transform the sql query in mongo but got stuck at a

Solution 1:

To achieve the desired result, start by breaking down the SQL query beginning with the sub query:

Select *
fromtblDataWhereTFNin (Select TFN From @tmpTFNList) andTrendDatebetween @StartDateAND @EndDate

The equivalent mongo query follows:

db.getCollection("_core.data").aggregate([
    {
        "$match": {
            "TFN": { "$in": tmpTFNList },
            "TrendDate": {
                "$gte": startDate,
                "$lte": endDate
            }
        }
    }
])

---

The $group aggregate equivalent of

Select TFN, Max(Impressions) MaxImpression 
from tblData 
Where TFN in (Select TFN From@tmpTFNList) and TrendDate between@StartDateAND@EndDateGroupby TFN 

follows

db.getCollection("_core.data").aggregate([
    {
        "$match": {
            "TFN": { "$in": tmpTFNList },
            "TrendDate": {
                "$gte": startDate,
                "$lte": endDate
            }
        }
    },
    {
        "$group": {
            "_id": "$TFN",
            "MaxImpression": { "$max": "$Impression" }
        }
    }
])

---

The top 5 query

SelectTop5a.TFN, a.MaxImpressionasMaxCountfrom ( 
    Select TFN, Max(Impressions) MaxImpression 
    from tblData 
    Where TFN in (Select TFN From @tmpTFNList) 
        and TrendDate between @StartDate AND @EndDate
    Group by TFN 
) a

is made possible with the $limit operator and the fields selection through the $project stage as

db.getCollection("_core.data").aggregate([
    { /* WHERE TFN in list AND TrendDate between DATES */"$match": {
            "TFN": { "$in": tmpTFNList },
            "TrendDate": {
                "$gte": startDate,
                "$lte": endDate
            }
        }
    },
    { /* GROUP BY TFN */"$group": {
            "_id": "$TFN",
            "MaxImpression": { "$max": "$Impression" }
        }
    },
    { "$limit": 5 }, /* TOP 5 */
    { /* SELECT a.MaxImpression as MaxCount */"$project": {
            "TFN": "$_id",
            "_id": 0,
            "MaxCount": "$MaxImpression"
        }
    }
])

---

UPDATE

To get the desired result from the sample in this edit, you need an extra $sort pipeline before the $group where your sort the documents by the TrendDate and Impression fields, both in descending order.

You will then have to use the $first accumulator operator within the $group pipeline stage to get the maximum impression as you will have an ordered stream of documents in your pipeline.

Consider running the revised aggregate operation as:

db.getCollection('collection').aggregate([
    { 
        "$match": {
            "TFN": { "$in": tmpTFNList },
            "TrendDate": {
                "$gte": startDate,
                "$lte": endDate
            }
        }
    },
    { "$sort": { "TrendDate": -1, "Impression": -1 } },
    {  
        "$group": {
            "_id": "$TFN",
            "MaxImpression": { "$first": "$Impression" }
        }
    },
    { "$limit": 5 }, 
    {   
        "$project": {
            "TFN": "$_id",
            "_id": 0,
            "MaxCount": "$MaxImpression"
        }
    }
])

Sample Output

/* 1 */{"TFN":84251456,"MaxCount":22}/* 2 */{"TFN":84251455,"MaxCount":35}