Data Manipulation with MongoDB Aggregation Framework in Python

MongoDB Aggregation Framework is a powerful tool that allows for data manipulation and analysis within MongoDB collections. It provides a flexible and efficient way to process and transform data, enabling users to perform complex operations such as grouping, sorting, filtering, and computing aggregate values. In this lab tutorial, we will introduce the concepts of MongoDB Aggregation Framework, provide a detailed explanation of the code, and walk through each line to understand its functionality. Visit the detailed tutorial here.

Code

import pymongo
from pymongo import MongoClient

# Connect to MongoDB
client = pymongo.MongoClient("mongodb+srv://user:pass@cluster0.ergtejf.mongodb.net/?retryWrites=true&w=majority&appName=Cluster0")

# Switch to the desired database
db = client.afzal
collection = db.test

# Insert sample data into a collection
db.collection.insert_many([
    { 'name': 'Afzal', 'age': 25, 'city': 'Islamabad' },
    { 'name': 'Jalal', 'age': 28, 'city': 'Mianwali' }
    { 'name': 'Yousaf', 'age': 30, 'city': 'Quetta' },
    { 'name': 'Ibrahim', 'age': 35, 'city': 'Karachi' },
])

print("Total documents in collection:", db.collection.count_documents({}))

# Calculate average age
pipeline_avg_age = [{ '$group': { '_id': None, 'avgAge': { '$avg': '$age' } } }]
avg_age_result = list(db.collection.aggregate(pipeline_avg_age))
print("Average age:", avg_age_result[0]['avgAge'])

# Group by city and count
pipeline_city_count = [{ '$group': { '_id': '$city', 'count': { '$sum': 1 } } }]
city_count_result = list(db.collection.aggregate(pipeline_city_count))
print("City count:", city_count_result)

# Group by city and find max age
pipeline_max_age = [{ '$group': { '_id': '$city', 'maxAge': { '$max': '$age' } } }]
max_age_result = list(db.collection.aggregate(pipeline_max_age))
print("Max age by city:", max_age_result)

# Filter documents where age is greater than 25
pipeline_filtered = [{ '$match': { 'age': { '$gt': 25 } } }]
filtered_result = list(db.collection.aggregate(pipeline_filtered))
print("Filtered documents:", filtered_result)

# Sort documents by age in descending order
pipeline_sorted = [{ '$sort': { 'age': -1 } }]
sorted_result = list(db.collection.aggregate(pipeline_sorted))
print("Sorted documents:", sorted_result)

Connection and Database Selection

• We import the necessary libraries pymongo and MongoClient.
• We establish a connection to the MongoDB server using the connection string (replace with your actual connection details).
• We specify the desired database (afzal) and collection (test) within the connected client.

import pymongo
from pymongo import MongoClient

# Connect to MongoDB (replace with your connection details)
client = pymongo.MongoClient("mongodb+srv://user:pass@cluster0.ergtejf.mongodb.net/?retryWrites=true&w=majority&appName=Cluster0")

# Switch to the desired database and collection (replace with your database and collection names)
db = client.afzal
collection = db.test

Sample Data and Counting Documents

• We insert sample data containing documents with names, ages, and cities.
• We use db.collection.insert_many to insert multiple documents at once.
• We then count the total number of documents in the collection using db.collection.count_documents({}). The empty dictionary {} specifies matching all documents.

# Insert sample data into a collection
db.collection.insert_many([
    { 'name': 'Afzal', 'age': 25, 'city': 'Islamabad' },
    { 'name': 'Jalal', 'age': 28, 'city': 'Mianwali' }
    { 'name': 'Yousaf', 'age': 30, 'city': 'Quetta' },
    { 'name': 'Ibrahim', 'age': 35, 'city': 'Karachi' },
])

print("Total documents in collection:", db.collection.count_documents({}))

Calculating Average Age

• We define an aggregation pipeline named pipeline_avg_age.
• The pipeline consists of a single stage using the $group operator.
  • _id: None discards the original document’s _id field in the output.
  • $avg: This operator calculates the average value of the age field and stores it in the avgAge field of the output document.
• We use db.collection.aggregate(pipeline_avg_age) to execute the pipeline and retrieve the results.
• We convert the results to a list using list and access the first element (since there’s only one document in the output) to get the average age stored in the avgAge field.

# Calculate average age
pipeline_avg_age = [{ '$group': { '_id': None, 'avgAge': { '$avg': '$age' } } }]
avg_age_result = list(db.collection.aggregate(pipeline_avg_age))
print("Average age:", avg_age_result[0]['avgAge'])

Grouping by City and Counting Documents

• The pipeline groups documents by their city using $group.
• _id: '$city' sets the group identifier to the city field value.
• $sum: 1 increments a counter for each document in the group, resulting in a count of documents for each city.
• We process and print the results similar to the previous step.

# Group by city and find max age
pipeline_max_age = [{ '$group': { '_id': '$city', 'maxAge': { '$max': '$age' } } }]
max_age_result = list(db.collection.aggregate(pipeline_max_age))
print("Max age by city:", max_age_result)

# Group by city and count
pipeline_city_count = [{ '$group': { '_id': '$city', 'count': { '$sum': 1 } } }]
city_count_result = list(db.collection.aggregate(pipeline_city_count))
print("City count:", city_count_result)

Grouping by City and Finding Maximum Age

• The pipeline groups documents by city using $group with _id: '$city'.
• $max: '$age' calculates the maximum value of the age field within each group, storing it in the maxAge field of the output document.
• We process and print the results as in previous steps.

pipeline_max_age = [{ '$group': { '_id': '$city', 'maxAge': { '$max': '$age' } } }]
max_age_result = list(db.collection.aggregate(pipeline_max_age))
print("Max age by city:", max_age_result)

Filtering Documents

• We define a pipeline with a single stage using the $match operator.
• $match filters documents based on a criteria. Here, it selects documents where the age is greater than ($gt) 25.
• We process and print the results, showcasing the filtered documents.

# Filter documents where age is greater than 25
pipeline_filtered = [{ '$match': { 'age': { '$gt': 25 } } }]
filtered_result = list(db.collection.aggregate(pipeline_filtered))
print("Filtered documents:", filtered_result)

Sorting Documents

• The pipeline uses the $sort operator to arrange documents.
• Here, age: -1 sorts documents by the age field in descending order (highest age first).
• We process and print the results, displaying the sorted documents.

# Sort documents by age in descending order
pipeline_sorted = [{ '$sort': { 'age': -1 } }]
sorted_result = list(db.collection.aggregate(pipeline_sorted))
print("Sorted documents:", sorted_result)