mongodb - 如何使用 Spring 从 MongoDB 中检索数据 - 按每天的最新日期时间分组?
问题描述
我在 MongoDB 中有以下格式的文档。我想提取按类别、名称、日期(格式如 yyyy-mm-dd)分组的数据,按每天的最新创建日期时间来了解通过或失败的状态。
{ "name":"Scenario number one", "category":"general", "creationDateTime":"2020-06-15T00:18:35.608+00:00", "status":"Passed" },
{ "name":"Scenario number one", "category":"general", "creationDateTime":"2020-06-15T00:11:35.608+00:00", "status":"Failed" },
{ "name":"Scenario number one", "category":"general", "creationDateTime":"2020-06-15T00:08:35.608+00:00", "status":"Passed" },
{ "name":"Scenario number two", "category":"happy-path", "creationDateTime":"2020-06-15T00:17:35.608+00:00", "status":"Failed" },
{ "name":"Scenario number one", "category":"general", "creationDateTime":"2020-06-13T00:12:35.608+00:00", "status":"Passed" },
{ "name":"Scenario number two", "category":"happy-path", "creationDateTime":"2020-06-12T00:11:35.608+00:00", "status":"Failed" },
{ "name":"Scenario number two", "category":"happy-path", "creationDateTime":"2020-06-12T00:10:35.608+00:00", "status":"Passed" }
我想要像这样的格式输出
{"category":"general", "name":"Scenario number one", "date":"2020-06-15", "status":"Passed"}
{"category":"happy-path", "name":"Scenario number two", "date":"2020-06-15", "status":"Failed"}
{"category":"general", "name":"Scenario number one", "date":"2020-06-13", "status":"Failed"}
{"category":"happy-path", "name":"Scenario number two", "date":"2020-06-12", "status":"Failed"}
请帮助使用 Spring Code。谢谢!
解决方案
Mongo 查询就像
[
{
$sort: {
"creationDateTime": -1
}
},
{
"$addFields": {
"date": {
"$substr": [
"$creationDateTime",
0,
10
]
}
}
},
{
"$group": {
"_id": {
name: "$name",
cat: "$category",
cdt: "$date"
},
"category": {
$first: "$category"
},
status: {
$first: "$status"
},
name: {
$first: "$name"
},
date: {
$first: "$date"
}
}
},
{
$project: {
_id: 0
}
}
]
工作Mongo 游乐场。而Spring中的聚合,
public List<Object> test() {
Aggregation aggregation = Aggregation.newAggregation(
sort(Sort.Direction.DESC, "creationDateTime"),
a -> new Document("$addFields",
new Document("date",
new Document("$substr", Arrays.asList("$creationDateTime", 0, 10))
)
),
group(Fields.from(
Fields.field("name", "$name"),
Fields.field("category", "$category"),
Fields.field("date", "$date")
))
.first("category").as("category")
.first("status").as("status")
.first("name").as("name")
.first("date").as("date"),
project().andExclude("_id")
).withOptions(AggregationOptions.builder().allowDiskUse(Boolean.TRUE).build());
return mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(YOUR_COLLECTION.class), Object.class).getMappedResults();
}
我没有测试它,但它应该可以工作,并且也可以看到这个。