PyMongo聚合查询返回空结果求助：新手寻求重复活动查询代码的错误定位

Hey there! Let's break down why your aggregation query is returning an empty list. I've spotted two key issues in your code, plus a note about your sample data that's contributing to the problem:

1. Missing transportation_mode Field in Your Data #

Your $group stage is set to group documents by user_id, transportation_mode, start_date_time, and end_date_time — but none of your sample documents include the transportation_mode field. In MongoDB, missing fields are treated as null, so every document in your sample will have null for this value. However, since all your start_date_time and end_date_time values are unique, each group ends up with a count of 1. The subsequent $match stage filters out groups where count > 1, which leaves no results to return.

If transportation_mode is meant to be part of your grouping logic, ensure this field exists in your actual database documents. If it's an optional field, you can adjust the grouping to handle missing values (e.g., using $ifNull to assign a default like "unknown").

2. Incorrect Field References in the $project Stage #

In your $project stage, you're referencing nested fields without the required $ prefix. MongoDB uses the $ to access values from the aggregation pipeline. For example, instead of "user_id":"_id.user_id", you need to write "user_id": "$_id.user_id". Without the $, you're just assigning the string "_id.user_id" to the user_id field instead of pulling the actual value from the grouped _id.

Corrected Query #

Here's the fixed version of your aggregation code:

rows = self.db.Activity.aggregate([
    {
        '$group': {
            "_id": {
                "user_id": "$user_id",
                # Uncomment below if you need to handle missing transportation_mode values
                # "transportation_mode": {"$ifNull": ["$transportation_mode", "unknown"]},
                "transportation_mode": "$transportation_mode",
                "start_date_time": "$start_date_time",
                "end_date_time": "$end_date_time"
            },
            "count": {'$sum': 1}
        }
    },
    {'$match': {"count": {'$gt': 1}}},
    {'$project': {
        "_id": 0,
        "user_id": "$_id.user_id",
        "transportation_mode": "$_id.transportation_mode",
        "start_date_time": "$_id.start_date_time",
        "end_date_time": "$_id.end_date_time",
        "count": 1
    }}
])

Testing the Fix #

To confirm the query works, add a duplicate document to your collection (e.g., copy one of your existing documents and insert it again). With a duplicate entry, the $group stage will create a group with count: 2, which will pass the $match filter and return the expected duplicate result.

内容的提问来源于stack exchange，提问作者Michael Maier