聚合批注无法使用“展开”和“匹配”

Question

我试图用Spring Boot和MongoDB运行一个自定义聚合查询，该查询返回子文档中符合特定条件的字段的总和。

目前，我有一个查询，返回子文档中字段的总和，但不考虑条件的匹配。

工作查询：

@Aggregation(pipeline = {"{'$project': {'_id': '$_id', 'date': '$date', \n" +
            "      'confirmed': {'$sum': '$states.confirmed'}, \n" +
            "      'deaths': {'$sum': '$states.deaths'}, \n" +
            "      'recovered': {'$sum': '$states.recovered'}, \n" +
            "      'active': {'$sum': '$states.active'}}}}"
})
AggregationResults<LineChart> aggregateAllStates(Sort sort);

返回：

[
    {
        "date": "2020-04-12",
        "confirmed": 555613,
        "deaths": 22107,
        "active": 500306,
        "recovered": 67139
    },
    {
        "date": "2020-04-13",
        "confirmed": 580876,
        "deaths": 23633,
        "active": 615609,
        "recovered": 78924
    },
    {
        "date": "2020-04-14",
        "confirmed": 607926,
        "deaths": 25931,
        "active": 534076,
        "recovered": 85408
    },
etc...
]

下一个聚合不起作用：

@Aggregation(pipeline = {"{'$unwind':{path:'$states'}},\n" +
            "  {'$match': {'states.state': 'alabama'}},\n" +
            "  {'$project':{_id:'$_id', date: '$date',\n" +
            "    confirmed: {'$sum': '$states.confirmed'},\n" +
            "    active: {'$sum': '$states.active'},\n" +
            "    deaths: {'$sum': '$states.deaths'},\n" +
            "    recovered: {'$sum': '$states.recovered'}\n" +
            "  }}"
})
AggregationResults<LineChart> aggregateOneState(String name, Sort sort);

返回：

[
    {
        "date": "2020-04-12",
        "confirmed": null,
        "deaths": null,
        "active": null,
        "recovered": null
    },
    {
        "date": "2020-04-12",
        "confirmed": null,
        "deaths": null,
        "active": null,
        "recovered": null
    },
    {
        "date": "2020-04-12",
        "confirmed": null,
        "deaths": null,
        "active": null,
        "recovered": null
    },
etc..
]

在mongo shell中运行此查询非常好。在Spring中，它返回更多的数据（非常确定它返回的是未卷绕的数据），所有字段都为空。

db.us_collection.aggregate([
  {'$unwind':{path:'$states'}},
  {'$match': {'states.state': 'alabama'}},
  {'$project':{_id:'$_id', date: '$date',
    confirmed: {'$sum': '$states.confirmed'},
    active: {'$sum': '$states.active'},
    deaths: {'$sum': '$states.deaths'},
    recovered: {'$sum': '$states.recovered'}
  }},
  {'$sort':{date:-1}}
  ])

编辑：

如何在MongoDB中的数据看起来

[
    {
        "id": "6017455f7517801dda88bc56",
        "date": "2020-04-12",
        "states": [
            {
                "state": "alabama",
                "confirmed": 3667,
                "deaths": 93,
                "recovered": 0,
                "active": 3470,
                "incident_rate": 75.98802021,
                "mortality_rate": 2.610159978
            },
            {
                "state": "alaska",
                "confirmed": 272,
                "deaths": 8,
                "recovered": 66,
                "active": 264,
                "incident_rate": 45.50404936,
                "mortality_rate": 2.941176471
            },
            {
                "state": "arizona",
                "confirmed": 3542,
                "deaths": 115,
                "recovered": 0,
                "active": 3427,
                "incident_rate": 48.66242224,
                "mortality_rate": 3.246753247
            },
            // continues list of states in same format
        ]
    },
    // more documents in same layout for different days
]

Answer 1

结果是在错误的地方出现了逗号。

如果在@Aggregation注释中有一个多步骤查询，那么它们必须在字符串外用逗号分隔。

无法工作的错误查询示例：

@Aggregation(pipeline = {"{'$unwind':{path:'$states'}},\n" +
            "  {'$match': {'states.state': 'alabama'}},\n" +
            "  {'$project':{_id:'$_id', date: '$date',\n" +
            "    confirmed: {'$sum': '$states.confirmed'},\n" +
            "    active: {'$sum': '$states.active'},\n" +
            "    deaths: {'$sum': '$states.deaths'},\n" +
            "    recovered: {'$sum': '$states.recovered'}\n" +
            "  }}"
})

请注意，分隔$unWind、$sex和$prject的逗号都在字符串中。

工作聚合查询示例：

@Aggregation(pipeline = {"{'$unwind':{path:'$states'}}",
            "  {'$match': {'states.state': 'alabama'}}",
            "  {'$project':{_id:'$_id', date: '$date',\n" +
            "    confirmed: {'$sum': '$states.confirmed'},\n" +
            "    active: {'$sum': '$states.active'},\n" +
            "    deaths: {'$sum': '$states.deaths'},\n" +
            "    recovered: {'$sum': '$states.recovered'}\n" +
            "  }}"
})

现在请注意，分隔逗号位于字符串之外。

我还接受了@prasad_的建议，删除了$unWind，而是使用过滤器来加速聚合。

这是我的最后一个工作聚合查询：

js prettyprint-override">@Aggregation(pipeline = {"" +
            "{'$project': {'_id': '$_id', 'date': '$date', \n" +
            "      'states': {'$filter': {\n" +
            "          'input': '$states', \n" +
            "          'as': 'item', \n" +
            "          'cond': {'$eq': ['$$item.state', '?0']}}}}" +
            "}",
            "{'$project': {'_id': '$_id', 'date': '$date', \n" +
            "      'confirmed': {'$sum': '$states.confirmed'}, \n" +
            "      'deaths': {'$sum': '$states.deaths'}, \n" +
            "      'recovered': {'$sum': '$states.recovered'}, \n" +
            "      'active': {'$sum': '$states.active'}}}" +
            "}"
})
AggregationResults<LineChart> aggregateOneState(String name, Sort sort);