Aquasar
  • Home
  • Portfolio
  • Articles
  • Pricing
  • About
  • Contact
WEB DEVELOPMENT |SEO |DIGITAL ADS

Aggregating data using mongo

Aug 16th, 2019

Alex Quasar

mongoaggregating data

For those familiar with querying and aggregating data via sql, doing it in using a nosql database like mongo is very similar. Though the syntax looks very different (everything is grouped into objects ) the concepts are the same.

Building off the previous article, express with mongoose, it assumes you understand and are familiar with sending a receiving data using express and mongoose.

Example 1:

// Type             :   GET
// Route            :   api/tasks/tour-stats
// Description      :   Get the tours stats
// Access           :   PUBLIC anyone can see stats
router.get('/tour-stats', async (req,res) => {
    try {
        const stats = await Tour.aggregate([
            {
                $match: { ratingsAverage: { $gt: 4.5 } }
            },
            {
                $group: { 
                    _id: {
                        difficulty:'$difficulty',
                        // averageRatings: '$ratingsAverage'
                    },
                    numTours:       { $sum  :1 },
                    numRatings:     { $sum  :'$ratingsQuantity' },
                    avgRating:      { $avg  :'$ratingsAverage' },
                    avgPrice:       { $avg  :'$price' },
                    minPrice:       { $min  :'$price' },
                    maxPrice:       { $max  :'$price' }
                }
            },
            {
                $sort: {avgPrice: 1}
            }
                

        ])
        res.json(stats);
    } catch (error) {
        res.status(500).json({msg:`Error in get request /tour-stats, ${error}`})
    }
}) 

1. $match, is a mongo operator similar to the WHERE clause in SQL. It filters the data to show only the data meeting the specified requirements.

2. $group, is a mongo operator similar to the GROUP BY clause in SQL. It groups the data specified inside the _id object which is required. You can specify multiple "columns" to group where the key is the name of the column you give and the value is the column name (prefixed by the $ sign)

If you don't want to group by anything, than specify id as null like _id : null

Also within the $group object, specify what you want to aggregate, where the key is again the name of the aggregation that you decide on and the value is an object where you specify the aggregation operator and the column name.

3. $sort, another mongo operator similar to SORT BY in SQL. When we get to sorting, our data is already grouped by the following columns specified in the group operation (ie. numTours, numRatings, etc). We have to pick one of these columns to sort by. Ascending is 1 and descending is -1.

Once you understand that everything is specified as objects in mongo, it becomes much easier to understand what is going on, even if the syntax might look a bit strange for those familiar with querying with a flavour of SQL.

Example 2:

// Type             :   GET
// Route            :   api/tasks/monthly-plan/:year
// Description      :   Get the most popular tour months
// Access           :   PUBLIC anyone can see stats
router.get('/monthly-plan/:year', async (req,res) => {
    try {
        const year = req.params.year *1;
        const plan = await Tour.aggregate([
            {
                $unwind: '$startDates'
            },
            {
                $match: { 
                    startDates: {
                        $gte: new Date(`${year}-01-01`),
                        $lt:  new Date(`${year+1}-01-01`)
                    }
                }
            },
            {
                $group: {
                    _id: {
                         $month: '$startDates'
                    },
                    countTours: {$sum: 1},
                    tours: { $push: '$name' }
                }
            },
            {
                $addFields: { 
                    month: '$_id' 
                }
            },
            {
                $sort:{
                    month: 1
                }
            },
            {
                $project: {
                    _id: 0
                }
            },
            {
                $limit: 10
            },
            {
                $addFields: {
                    month: {
                        $let: {
                            vars: {
                                monthsInString: [, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul','Aug','Sep','Oct','Nov','Dec']
                            },
                            in: {
                                $arrayElemAt: ['$$monthsInString', '$month']
                            }
                        }
                    }
                }
            }

        ]);

        res.json({dataItems: plan.length, data: plan });
    } catch (error) {
        
    }
})