2020年8月27日 19:29:51go评论68阅读模式

英文:

How to count array elements properly with multiple aggregation in 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>

城市集合（Cities collection）：

{ 
    "_id" : ObjectId("5f47878c95f47e209402fe15"), 
    "name" : "Warsaw",
    "description" : "Sample description"
}
{ 
    "_id" : ObjectId("5f4787918b343fff4f52c270"), 
    "name" : "Chicago",
    "description" : "Sample description"
}

图书馆集合（Libraries collection）：

{ 
    "_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")
}

图书集合（Books collection）：

{ 
    "_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个图书馆。

首先，我需要使用lookup计算图书馆数量，并返回 librariesCount - 这将是 2 和 1。
我需要获取/查找每个图书馆中的图书，然后计算它们的数量作为 '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._

<details>
<summary>英文:</summary>

I need to create advanced aggregation using Spring Data MongoDB having model like that:

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


Cities collection:

{
"_id" : ObjectId("5f47878c95f47e209402fe15"),
"name" : "Warsaw",
"description" : "Sample description"
}
{
"_id" : ObjectId("5f4787918b343fff4f52c270"),
"name" : "Chicago",
"description" : "Sample description"
}


Libraries collection:

{
"_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")
}


Books collection:

{
"_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")
}


Depending on the context of the user, I have to return cities with count of libraries and books in the city that can be filtered based on `startsWith()` or `like()` principle.
Assuming that I have 2 libraries in one city and 1 library in the other.
1. I need to count libraries using lookup first and return `librariesCount` - it will be `2` and `1`.
2. I need to fetch / lookup books in every library, then count them as &#39;booksCount&#39; and then multiply by `librariesCount` to get total amount of `booksCount` in the city (let&#39;s call it `cityBooksCount`).
I came up with aggregation like this:

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


Thanks to the help of one of the stackoverflow users I was able to obtain `librariesCount` in proper way. Unfortunately `cityBooksCount` always point to `0`. 
I&#39;m not so familiar with MongoDB, but I know that `$lookup` [operation is possible on array][1], so I&#39;ve tried mapping libraries array to list of `ObjectId`, but it&#39;s not working properly. Probably I&#39;m doing something wrong, but I don&#39;t know where is the problem. I get the proper amount of cities with other projected fields.
Can anyone tell me what I am doing wrong and how to correct it?
Thank you in advance.
[1]: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use-lookup-with-an-array
</details>
# 答案1
**得分**: 1
这可能会给您预期的答案。
```javascript
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 Data聚合操作。

英文:

This might be giving you the expected answer.

db.cities.aggregate([
{
&quot;$lookup&quot;: {
&quot;from&quot;: &quot;Libraries&quot;,
&quot;localField&quot;: &quot;_id&quot;,
&quot;foreignField&quot;: &quot;cityId&quot;,
&quot;as&quot;: &quot;libraries&quot;
}
},
{
$unwind: {
path: &quot;$libraries&quot;,
preserveNullAndEmptyArrays: true
}
},
{
&quot;$lookup&quot;: {
&quot;from&quot;: &quot;Books&quot;,
&quot;localField&quot;: &quot;libraries._id&quot;,
&quot;foreignField&quot;: &quot;libraryId&quot;,
&quot;as&quot;: &quot;books&quot;
}
},
{
$unwind: {
path: &quot;$books&quot;,
preserveNullAndEmptyArrays: true
}
},
{
$group: {
_id: &quot;$_id&quot;,
name: {
$first: &quot;$name&quot;
},
description: {
$first: &quot;$description&quot;
},
libraries: {
$push: &quot;$libraries&quot;
},
books: {
$push: &quot;$books&quot;
}
}
},
{
$project: {
_id: 1,
name: 1,
description: 1,
libraryCount: {
$size: &quot;$libraries&quot;
},
bookCount: {
$size: &quot;$books&quot;
}
}
}
])

As we discussed, there are some slight changes. Hope, you understood how to convert the mongo query to spring data aggregation.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何在Spring Data MongoDB中正确地使用多重聚合来计算数组元素？

问题

如何禁用调整大小，同时允许最大化？

Webflux 返回 Mono 并使用 Mono 中的信息设置标头

提供的方法必须返回一个值（不是 void）

Morphia java.util.Arrays$ArrayList cannot be cast to com.mongodb.DBObject when making a Projection.projection

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论