MongoDB: Grouping results by multiple fields (with examples)

Introduction

As a NoSQL database, MongoDB provides a powerful aggregation framework for transforming and combining data within the database. Grouping results by multiple fields is a common necessity when analyzing complex datasets with multiple variables. Through the MongoDB aggregation pipeline, we can manipulate and reshape documents in a collection according to our specific needs.

In this tutorial, we’ll examine how to effectively group documents by multiple fields using MongoDB’s aggregation framework. We’ll progress from basic examples to more advanced use cases, providing you with a solid grounding in grouping techniques in MongoDB.

Understanding the Aggregation Framework

The aggregation framework in MongoDB allows you to run data processing pipelines on your collections. These pipelines consist of multiple stages, through which documents are passed and transformed step by step.

The $group stage is a critical part of these pipelines. When grouping documents, you can use accumulator operators to perform operations on the data being grouped, such as summing, averaging, or counting occurrences.

Basic Grouping by a Single Field

Before we delve into multiple-field grouping, let’s start with the basics of using the $group stage with a single field.

db.collection.aggregate([
    {
        $group: {
            _id: '$category',
            count: { $sum: 1 }
        }
    }
]);

This example groups documents in the collection by the category field, counting the number of documents within each category.

Grouping by Multiple Fields

To group by multiple fields, you must specify an object as the _id of the $group stage with each field you want to group by.

db.collection.aggregate([
    {
        $group: {
            _id: {
                category: '$category',
                status: '$status'
            },
            count: { $sum: 1 }
        }
    }
]);

In this case, documents are grouped by both category and status, and the number of documents for each combination is counted.

Accumulating Other Fields

Beyond counting, you can accumulate other document fields using operators like $sum, $avg, $min, $max, and $first.

db.collection.aggregate([
    {
        $group: {
            _id: {
                category: '$category',
                year: '$year'
            },
            totalSales: { $sum: '$sales' },
            averagePrice: { $avg: '$price' }
        }
    }
]);

This example groups documents by category and year, then calculates the total sales and average price for each group.

Aggregation with Compound Grouping Conditions

You can also form more complex grouping conditions

db.collection.aggregate([
    {
        $group: {
            _id: {
                category: '$category',
                year: { $year: '$date' }
            },
            totalSales: { $sum: '$sales' }
        }
    }
]);

Here, documents are grouped by category and the year extruded from a date field using the $year date operator.

Sorting and Further Processing

After grouping, you might want to sort or further process your results. You can add additional stages to the aggregation pipeline to accomplish this.

db.collection.aggregate([
    { $group: { /* group stage */ } },
    { $sort: { totalSales: -1 } },
    { $limit: 10 }
]);

This pipeline first groups the documents, then sorts the resulting groups in descending order by total sales, and finally limits the result to the top 10.

Advanced Use Cases

For advanced use cases, you can incorporate additional stages like $match for filtering, $unwind for deconstructing arrays, and $project for reshaping the grouped data.

db.collection.aggregate([
    { $match: { status: 'A' } },
    { $unwind: '$items' },
    { $group: { /* group stage */ } },
    { $project: { /* project fields */ } },
    { $sort: { 'items.price': -1 } }
]);

In this pipeline, the $match stage filtrates documents by status, the $unwind deconstructs the items array, then it groups, projects fields and sorts the results by item price, descending.

Visualizing the Results

While the MongoDB shell can display aggregation results, visual tools like MongoDB Compass or third-party solutions can provide more powerful interfaces for inspecting and visualizing the output of complex aggregation queries.

Conclusion

Grouping by multiple fields using MongoDB’s aggregation framework offers flexibility to process and analyze data within the database. From elementary grouping to sophisticated data reshaping, MongoDB provides capabilities that can tackle a broad range of data aggregation tasks efficiently.