Aggregate Group by using minimum value and display count for each group

[Venkat_Swamy]

I am new MongoDB. I have a table like this below.

I would like to get the list of product categories who has minimum price within that category and their count.

ProdName PrdCategory PrdPrice

PrdB 34 8
PrdA 34 50
PrdC 134 49
PrdD 134 50
PrdE 34 8

My SQL works fine as shown below.

select PrdCategory, min(PrdPrice), count(*) from ProdTable
group by PrdCategory
order by PrdPrice, PrdCategory

My answer at SQL was:

PrdCategory min(PrdPrice) count
34 8 2
134 49 1

How will I get the same results with MongoDB, with a collection similar to ProdTable?

[Doug_Duncan]

Hello @Venkat_Swamy and welcome to the MongoDB community.

The following command will work but it’s messy:

db.products.aggregate([{
        $group: {
            _id: "$ProdCategory",
            minPrice: {
                $min: "$ProdPrice"
            },
            prices: {
                $push: "$ProdPrice"
            }
        }
    },
    {
        $unwind: "$prices"

    },
    {
        $match: {
            $expr: {
                $eq: ["$prices", "$minPrice"]
            }
        }
    },
    {
        $group: {
            _id: "$_id",
            minPrice: {
                $min: "$minPrice"
            },
            count: {
                $sum: 1
            }
        }
    }
])

For your sample documents it returns the results of:

[
  { _id: 134, minPrice: 49, count: 1 },
  { _id: 34, minPrice: 8, count: 2 }
]

You could add a final $project stage to rename the fields if you wanted to.

There’s probably a better solution out there that I’m too tired to see right now, but the above will give you something to play around with.

As always, this query works, but may not be efficient at higher amounts of data. Always test in your environment with production level data to see how it works for you. Filter out as as much as you can early in the pipeline so you don’t do unnecessary work in the pipeline.

[Venkat_Swamy]

Thank you Duncan. This works like a charm. Really appreciate your quick answer. Kudos to you!