Fix Random MongoDB Sorting in 5 Minutes (What Took Me 6 Hours to Debug)

The WTF Moment 🤯

Same query. Same parameters. Different results every time.

I stared at my terminal, refreshing the API response for the 10th time. Completely random results.

Shell

# First call - perfect ordering ✅
[Order #142, #139, #137, #128, #124...]
 
# Refresh - wait, what? 🤨
[Order #128, #142, #124, #137, #139...]
 
# Refresh again - seriously? 😤
[Order #137, #124, #142, #139, #128...]

The sorting wasn’t just broken. It was aggressively random.

The bug report seemed innocent enough: “Sorting works fine with deduplicate=false, but returns random order with deduplicate=true.”

Two identical API calls, same sort parameter (created_at:desc), but completely different results:

Shell

# Works perfectly - sorted by created_at descending ✅
curl "http://api.example.com/orders?user_id=123&sort=created_at:desc&deduplicate=false"
# Response: [#142 (10:00), #139 (09:45), #137 (09:30)...] - Perfect!

The cost?

• 18 support tickets in 24 hours
• Users questioning data integrity
• 3 emergency team meetings
• 6 hours of debugging for a 2-line fix

The kind of bug that makes you question your entire database architecture at 2 AM.

The twist? When deduplicate=true, we use MongoDB aggregation with $group to get only the latest order per customer. When false, we use a simple query with .order_by().

“But I’m sorting the data before grouping! This should work!”

— Me, 3 hours into debugging, coffee getting cold

---

Fighting this bug right now? Jump to The Complete Fix (2-minute read)

Want to understand why it happens? Keep reading for the full debugging story (10-minute read)

---

The Investigation 🔍

Here’s the code that looked perfectly innocent (spoiler: it wasn’t):

Python

# Original broken pipeline

The Complete Fix 🛠️

After 6 hours of debugging, 47 test runs, and way too much coffee, here’s the battle-tested solution:

Python

Testing Strategy 🧪

Verification is everything. Here’s how to prove your fix works:

Python

Hard-Won Lessons 📚

This 6-hour debugging marathon taught me more than I wanted to know:

1. Never assume stages preserve order

Each aggregation stage can reorder data. Read the docs for every stage. Assumptions will burn you.

2. Production data reveals what tests hide

Our test data had unique timestamps. Production had bulk imports creating 50 orders in the same millisecond. Edge cases are production cases.

3. Instability destroys trust fast

One user reported: “Every refresh shows different orders.” Within 24 hours, support had 18 similar tickets. Users assume bugs mean data corruption. Trust evaporates.

4. Comment the why, not just the what

Python

Look at that pipeline. It’s clean. It’s logical. Sort the data, group by customer_id taking the first (newest) order per customer, done.

I reviewed it 5 times. Showed it to 3 colleagues. Everyone nodded. “Looks fine to me.”

But MongoDB had other plans.

"""
Mock MongoDB aggregation test - demonstrates the sorting bug
"""
from pymongo import MongoClient
from datetime import datetime, timedelta
import random
 
# Setup in-memory MongoDB simulation
def setup_mock_database():
    """Create test data that reproduces the bug"""
    base_time = datetime(2026, 3, 15, 10, 0, 0)
 

Got random results? You have the bug. Keep reading for the fix.

Got consistent results? You might still be vulnerable. Here’s why.

Step 1: $match
   → 6 documents (user_id=123)
 
Step 2: $sort {created_at: -1}
   → Documents sorted: [Alice=10:00, Charlie=09:45, Bob=09:30, Alice=09:00, Bob=07:30, Alice=08:00]
   💬 "Perfect! Everything is sorted now..."
 
Step 3: $group by customer_id, take $first
   ✓ Alice's first: 10:00 (correct - it was first in sorted list)
   ✓ Bob's first: 09:30 (correct - it was first for Bob)
   ✓ Charlie's first: 09:45 (correct - only one)
   💬 "$first picked the right documents... we're golden!"
 
Step 4: $replaceRoot
   → 3 documents: [Alice=10:00, Bob=09:30, Charlie=09:45]
 
   🚨 PLOT TWIST: $group output is NOT SORTED!
   🎲 Order could be: [Bob=09:30, Alice=10:00, Charlie=09:45] (random!)
   🎲 Or: [Charlie=09:45, Alice=10:00, Bob=09:30]
   🎲 Or any other permutation MongoDB feels like returning
 
Step 5: $limit 10
   → Returns WRONG ORDER to client
   → Users refresh and see different results
   → Support tickets flood in
   → Your weekend is ruined

The first sort worked perfectly for its job (making $first pick correctly). But $group threw away the order.

def test_duplicate_timestamps():
    """What happens when orders share the exact same timestamp?"""
 
    # Real scenario: Bulk import creates these simultaneously
    orders = [
        {"_id": 1, "customer_id": "Alice", "created_at": datetime(2026, 3, 15, 10, 0, 0), "amount": 100},
        {"_id": 2, "customer_id": "Bob", "created_at": datetime(2026, 3, 15, 10, 0, 0), "amount": 200},  # Same time!
        {"_id": 3, "customer_id": "Charlie", "created_at": datetime(2026, 3, 15, 10, 0, 0), "amount": 300},  # Same time!
    ]
 
    collection.insert_many(orders)
 
    # Sorting by created_at alone = unstable
    pipeline_no_tiebreaker = [
        {"$sort": {"created_at": -1}},  # Only sort by time
        {"$limit": 10}
    ]
 
    # Run 3 times - watch the chaos
    print("❌ Without _id tiebreaker (unstable):")
    for i in range(3):
        results = list(collection.aggregate(pipeline_no_tiebreaker))
        order = [r["customer_id"] for r in results]
        print(f"  Run {i+1}: {order}")
        # Run 1: [Alice, Bob, Charlie]
        # Run 2: [Bob, Alice, Charlie]  ← Different!
        # Run 3: [Charlie, Bob, Alice]  ← Different again!
 
    # Adding _id tiebreaker = stability
    pipeline_with_tiebreaker = [
        {"$sort": {"created_at": -1, "_id": -1}},  # Add _id for deterministic order
        {"$limit": 10}
    ]
 
    print("\n✅ With _id tiebreaker (stable):")
    for i in range(3):
        results = list(collection.aggregate(pipeline_with_tiebreaker))
        order = [r["customer_id"] for r in results]
        print(f"  Run {i+1}: {order}")
        # Run 1: [Charlie, Bob, Alice]
        # Run 2: [Charlie, Bob, Alice]  ← Consistent!
        # Run 3: [Charlie, Bob, Alice]  ← Always the same!

Pro Tip: When primary sort fields can tie, MongoDB picks order arbitrarily. Add _id as the tiebreaker for deterministic results.

Solution: Always add _id as a tiebreaker (but respect user preferences if they already specified it):

Why the check? If a user explicitly requests sort=created_at:desc,_id:asc, respect their choice. Don’t silently override it. API consumers hate surprises.

def get_orders_with_deduplication(filter_params):
    """Get orders with customer_id deduplication and proper sorting"""
 
    # Parse sort params from user input
    sort_params = filter_params.get('sort')
    sort_fields = parse_sort_params(sort_params) if sort_params else ['-created_at']
 
    # Build sort query
    sort_query = {}
    for field in sort_fields:
        if field.startswith('-'):
            sort_query[field[1:]] = -1
        else:
            sort_query[field] = 1
 
    # Add _id tiebreaker for stable ordering (if not already specified)
    if '_id' not in sort_query:
        sort_query['_id'] = -1
 
    # Build aggregation pipeline
    pipeline = [
        {
            "$match": filter_params.get('filters', {})
        },
        {
            "$sort": sort_query  # Sort #1: Before $group for correct $first
        },
        {
            "$group": {
                "_id": "$customer_id",
                "doc": {"$first": "$$ROOT"}
            }
        },
        {
            "$replaceRoot": {"newRoot": "$doc"}
        },
        {
            "$sort": sort_query  # Sort #2: After $group for correct final order
        },
        {
            "$project": filter_params.get('fields', {})
        },
        {
            "$skip": filter_params.get('skip', 0)
        },
        {
            "$limit": filter_params.get('limit', 20)
        }
    ]
 
    results = list(Order.collection.aggregate(pipeline))
    return results

Copy this code. Test it. Ship it. Sleep peacefully.

• Multiple documents per group
• Duplicate timestamps
• High traffic (race conditions expose instability)

Test data with unique values won’t catch these edge cases.

Shareable insight: “In MongoDB aggregation, $group is a memory eraser for sort order. Always sort before (for accuracy) and after (for consistency).”

What to test:

• Run queries multiple times (at least 3-5 iterations)
• Test with duplicate timestamps
• Test with varying group sizes (1 doc/group vs many docs/group)
• Load test to expose race conditions
• Monitor production logs for inconsistent result complaints

Future you needs context.