Document modelling, indexes and aggregation in MongoDB

4 min read

Why it matters

Half of the Software Engineer job descriptions we read mention NoSQL, most often MongoDB. Document databases are easy to start with and easy to misuse: data modelled like relational tables, or queries without indexes, work fine in development and fall over in production.

The concept

Model for your queries. In a relational database you normalise first and join later. In a document database you ask "which data is read together?" and store it together.

Embed data that belongs to one parent and is read with it: an order's line items.
Reference data that is shared, large or changes independently: the customer, the product catalogue.
Watch document size (MongoDB's limit is 16 MB) and unbounded arrays, such as every event for a user in one document.

Index for your queries. Without an index, MongoDB scans every document (COLLSCAN). A compound index on the fields you filter by, followed by the fields you sort by, lets it read only the documents it needs (IXSCAN). Use explain() to check.

Aggregate in the database. The aggregation pipeline ($match`, `$unwind, $group`, `$project, $sort) does in one query what would otherwise mean pulling all documents into your application.

When not to use a document store. Data with many relationships queried in many directions, or strict multi-row transactions across entities, often fits a relational database better. PostgreSQL's JSON columns cover many "we need flexible fields" cases.

Worked example: 50,000 orders

Start MongoDB with docker run -d -p 27017:27017 mongo:8 and install the driver with pip install pymongo.

orders_mongo.py

import random
from datetime import datetime, timedelta, timezone

from pymongo import ASCENDING, DESCENDING, MongoClient

client = MongoClient("mongodb://localhost:27017", serverSelectionTimeoutMS=3000)
db = client.shop
db.orders.drop()

random.seed(7)
cities = ["Pune", "Delhi", "Bengaluru", "Chennai", "Kolkata"]
products = [("SKU-1", "Kettle", 1499), ("SKU-2", "Mixer", 3299), ("SKU-3", "Iron", 999), ("SKU-4", "Fan", 2499)]
start = datetime(2026, 1, 1, tzinfo=timezone.utc)

orders = []
for i in range(50_000):
    items = [
        {"sku": sku, "name": name, "qty": random.randint(1, 3), "price": price}
        for sku, name, price in random.sample(products, random.randint(1, 3))
    ]
    orders.append({
        "customer_id": random.randint(1, 5_000),
        "city": random.choice(cities),
        "placed_at": start + timedelta(minutes=10 * i),
        "status": random.choices(["delivered", "cancelled", "shipped"], [85, 10, 5])[0],
        "items": items,  # embedded: always read together with the order
    })
db.orders.insert_many(orders)
print("orders:", db.orders.count_documents({}))

query = {"customer_id": 42}
sort = [("placed_at", DESCENDING)]

def plan(cursor):
    winning = cursor.explain()["queryPlanner"]["winningPlan"]
    stages = []
    while winning:
        stages.append(winning["stage"])
        winning = winning.get("inputStage")
    stats = cursor.explain()["executionStats"]
    return " <- ".join(stages), stats["totalDocsExamined"], stats["nReturned"]

print("before index:", plan(db.orders.find(query).sort(sort).limit(5)))
db.orders.create_index([("customer_id", ASCENDING), ("placed_at", DESCENDING)])
print("after index: ", plan(db.orders.find(query).sort(sort).limit(5)))

pipeline = [
    {"$match": {"status": "delivered"}},
    {"$unwind": "$items"},
    {"$group": {"_id": "$city", "revenue": {"$sum": {"$multiply": ["$items.qty", "$items.price"]}}, "orders": {"$addToSet": "$_id"}}},
    {"$project": {"revenue": 1, "orders": {"$size": "$orders"}}},
    {"$sort": {"revenue": -1}},
]
for row in db.orders.aggregate(pipeline):
    print(f"{row['_id']:10} revenue={row['revenue']:>12,} orders={row['orders']:,}")

With MongoDB 8.3 and Python 3.11:

shellsession

$ python3 orders_mongo.py
orders: 50000
before index: ('SORT <- COLLSCAN', 50000, 5)
after index:  ('LIMIT <- FETCH <- IXSCAN', 5, 5)
Kolkata    revenue=  71,503,467 orders=8,534
Pune       revenue=  71,036,798 orders=8,581
Bengaluru  revenue=  70,473,162 orders=8,450
Chennai    revenue=  70,416,448 orders=8,500
Delhi      revenue=  69,683,634 orders=8,364

Before the index, finding one customer's five latest orders examined all 50,000 documents and sorted them in memory. After the compound index (customer_id, placed_at desc), it examined exactly 5: the index is already in the right order, so there is no separate sort. On a production collection with millions of documents, that is the difference between milliseconds and seconds.

Common mistakes

One collection per table. Copying a relational schema into MongoDB and then doing joins with $lookup everywhere.
Indexes in the wrong order. For filter-then-sort queries, put equality fields first, then sort fields, then range fields.
Too many indexes. Each one slows writes and uses memory. Index for real queries, and remove unused ones.
Unbounded arrays that grow forever inside one document.

Interview angle

"When would you choose MongoDB over PostgreSQL?", "embed or reference?", "how do you find a slow query?" (profiler or slow query log, then explain()), and "what is a compound index and why does field order matter?". For system design rounds, be ready to explain how a document store shards data by a shard key and why a bad key creates hot spots.

Practice task

Add a query for "all delivered orders in Pune in March 2026, newest first". Design the index, check the plan with explain() and record documents examined.
Add a customers collection and decide which customer fields, if any, to copy into each order. Write down what happens when a customer changes their address.

#Why it matters

#The concept

#Worked example: 50,000 orders

#Common mistakes

#Interview angle

#Practice task

#Further reading