The Aggregation Pipeline in Python

To count and average per group, MongoDB has the aggregation pipeline: a list of stages that aggregate runs in order, each stage getting the documents the one before passed on. A stage is a dict with one key. $match keeps the documents that pass a filter, like WHERE; $group makes one document per group, like GROUP BY; $sort orders them, like ORDER BY.

In $group, _id is what you group by, and every other key is computed for each group, with $sum or $avg for example. A string that starts with $ means a field's value: "$status" is each order's status. This pipeline counts the orders per status:

pipeline = [
    {"$group": {"_id": "$status", "orders": {"$sum": 1}}},
    {"$sort": {"_id": 1}},
]
for row in db.orders.aggregate(pipeline):
    print(row["_id"], row["orders"])

Your pipeline works on the products that are in stock and prints one line per category: the category, how many products it has and their average price, highest average first. The Gift card has no category, so its group's _id is None. This lesson runs on an in-memory MongoDB that already holds the shop; the same code runs on a real MongoDB server.

None 1 50.0
home 2 22.0
kitchen 2 17.0
office 2 5.5

Your task: fill the pipeline list with three stages: a $match that keeps the products whose stock.count is over 0, a $group by "$category" that counts its products and computes their avg_price, and a $sort on avg_price, highest first. Then loop over db.products.aggregate(pipeline) and print each row's _id, products and avg_price.

Assignment
Follow the Coding Tutorial to build the pipeline one stage at a time, then run it with aggregate and print each group.

Hint
Put $sort after $group: only then do the documents have an avg_price. Field values need the $, as in "$price"; the names of the keys you make don't.