Count操作在Spring Data MongoDB聚合中对数组进行分组后返回1而不是0。

huangapple go评论122阅读模式
英文:

Count operation returns 1 instead of 0 after grouping array in Spring Data MongoDB Aggregation

问题

我需要使用 Spring Data MongoDB 创建高级聚合,其模型如下:

@Getter
@Setter
@Document
public class Library {

  @Id
  @JsonSerialize(using = ToStringSerializer.class)
  private ObjectId id;

  private Address address;

  private String workingHours;

  // ...

}

@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("5f45440ee89590218e83a697"), 
    "workingHours" : "8:00 PM - 8:00 AM",
    "address" : DBRef("addresses", ObjectId("5f4544198da452a5523e3d11"))
}

图书集合:

{ 
    "_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() 原则进行过滤的不同数量的图书。

假设我有 4 本已发布的图书,普通用户添加了一本,另外还有一本是隐藏的。

  1. 管理员应该知道所有这些,所以他将看到 booksCount6
  2. 普通用户只能看到已发布的或自己添加的图书,所以他将看到 booksCount5
  3. 将来可能会有其他条件。

我提出了如下的聚合方法:

Criteria criteria = Criteria.where("_id").ne(null).and("address.city").is("Chicago");

MatchOperation matchOperation = Aggregation.match(criteria);

LookupOperation lookupOperation = LookupOperation.newLookup().from("books").localField("_id").foreignField("topicId").as("books");

UnwindOperation unwindOperation = Aggregation.unwind("books", true);

MatchOperation secondMatchOperation = Aggregation.match(Criteria.where("books.published").is(Boolean.TRUE).orOperator(Criteria.where("creator.userId").is(context.getUserId())));

AggregationOperation group = Aggregation.group("_id")
            .first("_id").as("id")
            .first("published").as("published")
            .first("title").as("title")
            .push("books").as("books").count().as("booksCount");

Aggregation aggregation = !isAdministrator() ?
Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, secondMatchOperation, group) : 
Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, group);
            
mongoTemplate.aggregate(aggregation, "libraries", Document.class).getRawResults().get("results");

除了 count() 操作之外,一切正常。

  1. 如果图书数组大小为 0,它总是返回 1。
  2. 如果图书数组大小大于 0,它会返回正确的数量。
  3. 我尝试使用 newBuilder(GroupOps.SUM, null, 0) 替代 count(),但现在它总是返回 0。
  4. 如果我使用 newBuilder(GroupOps.SUM, null, 2),它会返回 size + 2。我不知道发生了什么。

我的问题:

  1. 有人能告诉我我做错了什么,如何纠正吗?
  2. 另外,我需要将 "booksCount" 从 Integer 解析为 String。在分组阶段是否可能?

提前感谢您的帮助。

英文:

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

@Getter
@Setter
@Document
public class Library {

  @Id
  @JsonSerialize(using = ToStringSerializer.class)
  private ObjectId id;

  private Address address;

  private String workingHours;

  ...

}

@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

&lt;dependency&gt;
        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
        &lt;artifactId&gt;spring-boot-starter-data-mongodb&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
        &lt;groupId&gt;org.springframework.data&lt;/groupId&gt;
        &lt;artifactId&gt;spring-data-mongodb&lt;/artifactId&gt;
        &lt;version&gt;2.2.0&lt;/version&gt;
&lt;/dependency&gt;

Libraries collection:

{ 
    &quot;_id&quot; : ObjectId(&quot;5f45440ee89590218e83a697&quot;), 
    &quot;workingHours&quot; : &quot;8:00 PM - 8:00 AM&quot;,
    &quot;address&quot; : DBRef(&quot;addresses&quot;, ObjectId(&quot;5f4544198da452a5523e3d11&quot;))
}

Books collection:

{ 
    &quot;_id&quot; : ObjectId(&quot;5f454423be823729015661ed&quot;), 
    &quot;published&quot;: true,
    &quot;hidden&quot;: false,
    &quot;title&quot;: &quot;The Hobbit, or There and Back Again&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
},
{ 
    &quot;_id&quot; : ObjectId(&quot;5f45445b876d08649b88ed5a&quot;), 
    &quot;published&quot;: true,
    &quot;hidden&quot;: false,
    &quot;title&quot;: &quot;Harry Potter and the Philosopher&#39;s Stone&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
},
{ 
    &quot;_id&quot; : ObjectId(&quot;5f45446c7e33ca70363f629a&quot;), 
    &quot;published&quot;: true,
    &quot;hidden&quot;: false,
    &quot;title&quot;: &quot;Harry Potter and the Cursed Child&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
},
{ 
    &quot;_id&quot; : ObjectId(&quot;5f45447285f9b3e4cb8739ad&quot;), 
    &quot;published&quot;: true,
    &quot;hidden&quot;: false,
    &quot;title&quot;: &quot;Fantastic Beasts and Where to Find Them&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
},
{ 
    &quot;_id&quot; : ObjectId(&quot;5f45449fc121a20afa4fbb96&quot;), 
    &quot;published&quot;: false,
    &quot;hidden&quot;: false,
    &quot;title&quot;: &quot;Universal Parks &amp; Resorts&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
},
{ 
    &quot;_id&quot; : ObjectId(&quot;5f4544a5f13839bbe89edb23&quot;), 
    &quot;published&quot;: false,
    &quot;hidden&quot;: true,
    &quot;title&quot;: &quot;Ministry of Dawn&quot;
    &quot;libraryId&quot;: ObjectId(&quot;5f45440ee89590218e83a697&quot;)
}

Depending on the context of the user, I have to return a different count of books that can be filtered based on startsWith() or like() principle.

Assuming that I have 4 published books, one more added by normal user and one more hidden.

  1. Administrator should know about all of them, so he will see booksCount as 6.
  2. The regular user sees only published or added by himself, so he will see booksCount as 5.
  3. There can be some other conditions in the future.

I came up with aggregation like this:

Criteria criteria = Criteria.where(&quot;_id&quot;).ne(null).and(&quot;address.city&quot;).is(&quot;Chicago&quot;);

MatchOperation matchOperation = Aggregation.match(criteria);
			
LookupOperation lookupOperation = LookupOperation.newLookup().from(&quot;books&quot;).localField(&quot;_id&quot;).foreignField(&quot;topicId&quot;).as(&quot;books&quot;);

UnwindOperation unwindOperation = Aggregation.unwind(&quot;books&quot;, true);

MatchOperation secondMatchOperation = Aggregation.match(Criteria.where(&quot;books.published&quot;).is(Boolean.TRUE).orOperator(Criteria.where(&quot;creator.userId&quot;).is(context.getUserId()));

AggregationOperation group = Aggregation.group(&quot;_id&quot;)
            .first(&quot;_id&quot;).as(&quot;id&quot;)
            .first(&quot;published&quot;).as(&quot;published&quot;)
            .first(&quot;title&quot;).as(&quot;title&quot;)
            .push(&quot;books&quot;).as(&quot;books&quot;).count().as(&quot;booksCount&quot;);

Aggregation aggregation = !isAdministrator() ?
Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, secondMatchOperation, group) : 
Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, group);
			
mongoTemplate.aggregate(aggregation, &quot;libraries&quot;, Document.class).getRawResults().get(&quot;results&quot;);

Everything works fine instead of count() operation.

  1. If books array size is 0 it always returns 1.
  2. If books array size is greater than 0 it returns the proper amount.
  3. I've tried using newBuilder(GroupOps.SUM, null, 0) instead of count(), but now it always return 0.
  4. If I use newBuilder(GroupOps.SUM, null, 2) it returns size + 2. I don't know what is going on.

My quesions:

  1. Can anyone tell me what I am doing wrong and how to correct it?
  2. In addition I need to parse "booksCount" from Integer to String. Is it possible in group stage?

Thank you in advance.

答案1

得分: 2

因为 Aggregation.unwind(&quot;books&quot;, true); 导致了这种情况。当没有连接时,该语句会将文档保留下来,除非你将其设置为 Aggregation.unwind(&quot;books&quot;)。默认行为是 false。因此,在计数时,该文档被计算为一个文档。这就是为什么它以 1 作为输出的原因。错误输出示例

所以你可以在下一个阶段中计算大小。

project(&quot;_id&quot;, &quot;published&quot;, &quot;title&quot;, &quot;books&quot;)
   .and(ArrayOperators.Size.lengthOfArray(ConditionalOperators.ifNull(&quot;books&quot;).then(Collections.emptyList()))).as(&quot;booksCount&quot;)

Mongo playground 中的正确答案 正常工作。

英文:

It happens because of Aggregation.unwind(&quot;books&quot;, true);. When there is no join, this statement keeps as the document unless you make it as Aggregation.unwind(&quot;books&quot;). Default behavior is false. Then when you counting, the document is counted as a document. That's why its giving you the 1 as output. Example with wrong output

So what you can do is, you can count the size in the next stage.

 project(&quot;_id&quot;, &quot;published&quot;, &quot;title&quot;, &quot;books&quot;)
   .and(ArrayOperators.Size.lengthOfArray(ConditionalOperators.ifNull(&quot;books&quot;).then(Collections.emptyList()))).as(&quot;booksCount&quot;)

Working Mongo playground with correct answer

huangapple
  • 本文由 发表于 2020年8月26日 19:53:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/63597076.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定