java - 如何在 Spring Data MongoDB 中使用多个聚合正确计算数组元素?
问题描述
我需要使用具有以下模型的 Spring Data MongoDB 创建高级聚合:
@Getter
@Setter
@Document
public class City {
@Id
@JsonSerialize(using = ToStringSerializer.class)
private ObjectId id;
private Address description;
private String name;
...
}
@Getter
@Setter
@Document
public class Library {
@Id
@JsonSerialize(using = ToStringSerializer.class)
private ObjectId id;
private Address address;
private String workingHours;
@JsonSerialize(using = ToStringSerializer.class)
private ObjectId cityId;
...
}
@Getter
@Setter
@Document
public class Book {
@Id
@JsonSerialize(using = ToStringSerializer.class)
private ObjectId id;
private Boolean published;
private Boolean hidden;
private String title;
@JsonSerialize(using = ToStringSerializer.class)
private ObjectId libraryId;
...
}
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-mongodb</artifactId>
<version>2.2.0</version>
</dependency>
城市集合:
{
"_id" : ObjectId("5f47878c95f47e209402fe15"),
"name" : "Warsaw",
"description" : "Sample description"
}
{
"_id" : ObjectId("5f4787918b343fff4f52c270"),
"name" : "Chicago",
"description" : "Sample description"
}
图书馆收藏:
{
"_id" : ObjectId("5f45440ee89590218e83a697"),
"workingHours" : "8:00 PM - 8:00 AM",
"address" : DBRef("addresses", ObjectId("5f4544198da452a5523e3d11")),
"cityId": ObjectId("5f47878c95f47e209402fe15")
},
{
"_id" : ObjectId("5f478725d1507323a80efa31"),
"workingHours" : "8:00 PM - 8:00 AM",
"address" : DBRef("addresses", ObjectId("5f4787379e72f882e4d26912")),
"cityId": ObjectId("5f47878c95f47e209402fe15")
},
{
"_id" : ObjectId("5f47872f7c4872d4983961f5"),
"workingHours" : "8:00 PM - 8:00 AM",
"address" : DBRef("addresses", ObjectId("5f47873d5ddedadb3d6ddd6e")),
"cityId": ObjectId("5f4787918b343fff4f52c270")
}
书籍收藏:
{
"_id" : ObjectId("5f454423be823729015661ed"),
"published": true,
"hidden": false,
"title": "The Hobbit, or There and Back Again"
"libraryId": ObjectId("5f45440ee89590218e83a697")
},
{
"_id" : ObjectId("5f45445b876d08649b88ed5a"),
"published": true,
"hidden": false,
"title": "Harry Potter and the Philosopher's Stone"
"libraryId": ObjectId("5f45440ee89590218e83a697")
},
{
"_id" : ObjectId("5f45446c7e33ca70363f629a"),
"published": true,
"hidden": false,
"title": "Harry Potter and the Cursed Child"
"libraryId": ObjectId("5f45440ee89590218e83a697")
},
{
"_id" : ObjectId("5f45447285f9b3e4cb8739ad"),
"published": true,
"hidden": false,
"title": "Fantastic Beasts and Where to Find Them"
"libraryId": ObjectId("5f45440ee89590218e83a697")
},
{
"_id" : ObjectId("5f45449fc121a20afa4fbb96"),
"published": false,
"hidden": false,
"title": "Universal Parks & Resorts"
"libraryId": ObjectId("5f45440ee89590218e83a697")
},
{
"_id" : ObjectId("5f4544a5f13839bbe89edb23"),
"published": false,
"hidden": true,
"title": "Ministry of Dawn"
"libraryId": ObjectId("5f45440ee89590218e83a697")
}
根据用户的上下文,我必须返回城市中可以根据startsWith()
或like()
原则过滤的图书馆和书籍的数量。
假设我在一个城市有 2 个图书馆,在另一个城市有 1 个图书馆。
- 我需要先使用查找来计算库并返回
librariesCount
- 它将是2
and1
。 - 我需要在每个图书馆中获取/查找书籍,然后将它们计为“booksCount”,然后乘以
librariesCount
得到城市的总量booksCount
(我们称之为cityBooksCount
)。
我想出了这样的聚合:
Criteria criteria = Criteria.where("_id");
MatchOperation matchOperation = Aggregation.match(criteria);
LookupOperation lookupOperation = LookupOperation.newLookup().from("libraries").localField("_id").foreignField("cityId").as("libraries");
UnwindOperation unwindOperation = Aggregation.unwind("libraries", true);
LookupOperation secondLookupOperation = LookupOperation.newLookup().
from("books").
localField("libraryIdArray").
foreignField("libraryId").
as("books");
UnwindOperation secondUnwindOperation = Aggregation.unwind("books", true);
AggregationOperation group = Aggregation.group("_id")
.first("_id").as("id")
.first("name").as("name")
.first("description").as("description")
.push("libraries").as("libraries")
.push("books").as("books");
ProjectionOperation projectionOperation = Aggregation.project("id", "description", "name")
.and(VariableOperators.mapItemsOf(ConditionalOperators.ifNull("libraries").then(Collections.emptyList()))
.as("library").andApply(aggregationOperationContext -> {
Document document = new Document();
document.append("id", "$$library._id");
return document;
})).as("libraryIdArray")
.and(ConvertOperators.valueOf(ArrayOperators.Size.lengthOfArray(ConditionalOperators.ifNull("libraries").then(Collections.emptyList()))).convertToString()).as("librariesCount")
.and(ConvertOperators.valueOf(ArrayOperators.Size.lengthOfArray(ConditionalOperators.ifNull("books").then(Collections.emptyList()))).convertToString()).as("cityBooksCount");
Aggregation aggregation = Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, secondLookupOperation, secondUnwindOperation, group, projectionOperation);
mongoTemplate.aggregate(aggregation, "cities", Document.class).getRawResults().get("results");
感谢其中一位 stackoverflow 用户的帮助,我能够以librariesCount
适当的方式获得。可惜cityBooksCount
总是指向0
。
我对 MongoDB 不太熟悉,但我知道$lookup
可以在 array 上进行操作,所以我尝试将库 array 映射到 list of ObjectId
,但它不能正常工作。可能我做错了什么,但我不知道问题出在哪里。我得到了适当数量的具有其他投影字段的城市。
谁能告诉我我做错了什么以及如何纠正它?
先感谢您。
解决方案
这可能会给你预期的答案。
db.cities.aggregate([
{
"$lookup": {
"from": "Libraries",
"localField": "_id",
"foreignField": "cityId",
"as": "libraries"
}
},
{
$unwind: {
path: "$libraries",
preserveNullAndEmptyArrays: true
}
},
{
"$lookup": {
"from": "Books",
"localField": "libraries._id",
"foreignField": "libraryId",
"as": "books"
}
},
{
$unwind: {
path: "$books",
preserveNullAndEmptyArrays: true
}
},
{
$group: {
_id: "$_id",
name: {
$first: "$name"
},
description: {
$first: "$description"
},
libraries: {
$push: "$libraries"
},
books: {
$push: "$books"
}
}
},
{
$project: {
_id: 1,
name: 1,
description: 1,
libraryCount: {
$size: "$libraries"
},
bookCount: {
$size: "$books"
}
}
}
])
正如我们所讨论的,有一些细微的变化。希望您了解如何将 mongo 查询转换为 spring 数据聚合。
推荐阅读
- firebase - Gatsby 客户端仅路径在生产中的浏览器中首次加载时显示 404
- sql - 如何在 BigQuery SQL 中计算 SUM 的百分比?
- c# - 如何将 CRC32 哈希添加到作为 ByteRangeStreamContent 返回的数据中?
- sql - 将 SQL 查询转换为 Django ORM
- javascript - 如何用数字或数字键盘停止 alt 键?
- bash - IP 中的 Telnet 和文件中的端口(第一列是 IP,第二列是 PORT )
- sql - [SQL][DB2] 无记录时返回 0
- android - 应用程序运行时,SharedPreferences 的 Android 值未更新
- ios - SwiftUI:如何处理 BLE 响应并显示新值
- android - 抽屉导航不隐藏屏幕