聚合注释不能与Unwind和Match一起工作

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、$match和$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，而是使用过滤器来加速聚合。

这是我最终的聚合查询:

@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);