MongoDB - 聚合阶段 $count、$skip、$project
文章目录
1. $count 聚合阶段
计算匹配到的文档数量：
db.collection.aggregate([
// 匹配条件
{ $match: { field: "value" } },
// 其他聚合阶段
// ...
// 计算匹配到的文档数量
{ $count: "total" }
])
首先使用 $match 阶段来筛选出满足条件的文档。然后可以添加其他的聚合阶段来进行进一步的数据处理。最后，使用 $count 阶段来计算匹配到的文档数量，并将结果存储在一个字段中（在示例中是 “total”）。
请注意，$count 阶段只返回一个文档，其中包含一个字段，表示匹配到的文档数量。如果没有匹配到任何文档，将返回一个文档，该字段的值为 0。
构造测试数据：
db.scores.drop()
db.scores.insertMany([
{ "_id" : 1, "subject" : "History", "score" : 88 },
{ "_id" : 2, "subject" : "History", "score" : 92 },
{ "_id" : 3, "subject" : "History", "score" : 97 },
{ "_id" : 4, "subject" : "History", "score" : 71 },
{ "_id" : 5, "subject" : "History", "score" : 79 },
{ "_id" : 6, "subject" : "History", "score" : 83 }
])
$match阶段筛选 score 大于 80 的文档并计算匹配到的文档数量：
db.scores.aggregate(
[
// 第一阶段
{
$match: {
score: {
$gt: 80
}
}
},
// 第二阶段
{
$count: "passing_scores"
}
]
)
{ "passing_scores" : 4 }
SpringBoot整合MongoDB实现：
// 输入文档实体类
@Data
@Document(collection = "scores")
public class Score {
private int _id;
private String subject;
private int score;
}
// 输出文档实体类
@Data
public class AggregationResult {
private String passing_scores;
}
// 聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void aggregateTest() {
// $match 聚合阶段
Criteria criteria = Criteria.where("score").gt(80);
MatchOperation match = Aggregation.match(criteria);
// $count 聚合阶段
CountOperation count = Aggregation.count().as("passing_scores");
// 组合聚合阶段
Aggregation aggregation = Aggregation.newAggregation(match,count);
// 执行聚合查询
AggregationResults<AggregationResult> results
= mongoTemplate.aggregate(aggregation, Score.class, AggregationResult.class);
List<AggregationResult> mappedResults = results.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
//AggregationResult(passing_scores=4)
}
}
2. $skip 聚合阶段
$skip 用于跳过指定数量的文档，并将剩余的文档传递给下一个聚合阶段。
{ $skip: <num> }
构造测试数据：
db.scores.drop()
db.scores.insertMany([
{ "_id" : 1, "subject" : "History", "score" : 88 },
{ "_id" : 2, "subject" : "History", "score" : 92 },
{ "_id" : 3, "subject" : "History", "score" : 97 },
{ "_id" : 4, "subject" : "History", "score" : 71 },
{ "_id" : 5, "subject" : "History", "score" : 79 },
{ "_id" : 6, "subject" : "History", "score" : 83 }
])
db.scores.aggregate([
{ $skip : 3 }
]);
// 1
{
"_id": 4,
"subject": "History",
"score": 71
}
// 2
{
"_id": 5,
"subject": "History",
"score": 79
}
// 3
{
"_id": 6,
"subject": "History",
"score": 83
}
$skip 跳过管道传递给它的前 3 个文档，并将剩余的文档传递给下一个聚合阶段。
// 输入文档实体类
@Data
@Document(collection = "scores")
public class Score {
private int _id;
private String subject;
private int score;
}
// 输出文档实体类
@Data
public class AggregationResult {
private int _id;
private String subject;
private int score;
}
// 聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void aggregateTest() {
// $skip 聚合阶段
SkipOperation skip = Aggregation.skip(3);
// 组合聚合阶段
Aggregation aggregation = Aggregation.newAggregation(skip);
// 执行聚合查询
AggregationResults<AggregationResult> results
= mongoTemplate.aggregate(aggregation, Score.class, AggregationResult.class);
List<AggregationResult> mappedResults = results.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
//AggregationResult(_id=4, subject=History, score=71)
//AggregationResult(_id=5, subject=History, score=79)
//AggregationResult(_id=6, subject=History, score=83)
}
}
3. $project 聚合阶段
将带所请求字段的文档传递至管道中的下个阶段。$project 返回的文档可以指定包含字段、排除 _id 字段、添加新字段以及计算现有字段的值。
$project 阶段具有以下原型形式：
{ $project: { <specification(s)> } }
1. 包含指定字段
默认情况下，_id 字段包含在输出文档中。要在输出文档中包含输入文档中的任何其他字段，必须在 $project指定，如果您指定包含的字段在文档中并不存在，那么 $project 将忽略该字段包含，同时不会将该字段添加到文档中。
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5
}
)
以下 $project 阶段输出文档中仅包含 _id、title 和 author 字段：
db.books.aggregate( [
{ $project: { title: 1 , author: 1 } }
] )
{
"_id": 1,
"title": "abc123",
"author": {
"last": "zzz",
"first": "aaa"
}
}
SpringBoot 整合 MongoDB实现：
// 输入文档实体类
@Data
@Document(collection = "books")
public class Book {
private int _id;
private String title;
private String isbn;
private Author author;
private int copies;
@Data
public static class Author {
private String last;
private String first;
}
}
// 输出文档实体类
@Data
public class AggregationResult {
private int _id;
private String title;
private Author author;
@Data
public static class Author {
private String last;
private String first;
}
}
// 聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void testAggregate(){
// project 阶段
ProjectionOperation project = Aggregation.project("title", "author");
// 组合阶段
Aggregation aggregation = Aggregation.newAggregation(project);
// 执行聚合
AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Book.class, AggregationResult.class);
List<AggregationResult> mappedResults = aggregationResults.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
// AggregationResult(_id=1, title=abc123, author=AggregationResult.Author(last=zzz, first=aaa))
}
}
2. 排除_id字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5
}
)
以下 $project 阶段输出文档中仅包含 title 和 author 字段：
db.books.aggregate( [
{ $project: { _id: 0, title: 1, author: 1 } }
] )
{
"title": "abc123",
"author": {
"last": "zzz",
"first": "aaa"
}
}
SpringBoot 整合 MongoDB实现：
// 输入文档实体类
@Data
@Document(collection = "books")
public class Book {
private int _id;
private String title;
private String isbn;
private Author author;
private int copies;
@Data
public static class Author {
private String last;
private String first;
}
}
// 输出文档实体类
@Data
public class AggregationResult {
private int _id;
private String title;
private String isbn;
private Author author;
private int copies;
@Data
public static class Author {
private String last;
private String first;
}
}
// 聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void testAggregate(){
// project 阶段
ProjectionOperation project = Aggregation.project("title", "author").andExclude("_id");
// 组合阶段
Aggregation aggregation = Aggregation.newAggregation(project);
// 执行聚合
AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Book.class, AggregationResult.class);
List<AggregationResult> mappedResults = aggregationResults.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
// AggregationResult(title=abc123, author=AggregationResult.Author(last=zzz, first=aaa))
}
}
3. 排除指定字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5,
lastModified: "2016-07-28"
}
)
在 $project 阶段中输出文档排除 title 和 isbn 字段：
db.books.aggregate( [
{ $project: { title: 0, isbn: 0 } }
] )
// 1
{
"_id": 1,
"author": {
"last": "zzz",
"first": "aaa"
},
"copies": 5,
"lastModified": "2016-07-28"
}
SpringBoot 整合 MongoDB实现：
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void testAggregate(){
// project 阶段
ProjectionOperation project = Aggregation.project().andExclude("isbn","title");
// 组合阶段
Aggregation aggregation = Aggregation.newAggregation(project);
// 执行聚合
AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Book.class, AggregationResult.class);
List<AggregationResult> mappedResults = aggregationResults.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
// AggregationResult(_id=1, title=null, isbn=null, author=AggregationResult.Author(last=zzz, first=aaa), copies=5, lastModified=2016-07-28)
}
}
4. 不能同时指定包含字段和排除字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5,
lastModified: "2016-07-28"
}
)
在 $project 阶段中输出文档排除 title 和 isbn 字段：
db.books.aggregate( [
{ $project: { title: 0, isbn: 1 } }
] )
报错信息：
[Error] Bad projection specification, cannot include fields or add computed fields during an exclusion projection: { title: 0.0, isbn: 1.0 }
原因分析：在投影规范中，除_id字段外，不要在包含投影规范中使用排除操作。下面这样是可以的：
db.books.aggregate( [
{ $project: { _id: 0, isbn: 1 } }
] )
5. 排除嵌入式文档中的指定字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5,
lastModified: "2016-07-28"
}
)
以下 $project 阶段中输出文档排除 author.first 和 lastModified 字段：
db.books.aggregate( [
{ $project: { "author.first": 0, lastModified: 0 } }
] )
{
"_id": 1,
"title": "abc123",
"isbn": "0001122223334",
"author": {
"last": "zzz"
},
"copies": 5
}
6. 包含嵌入式文档中的指定字段
构造测试数据：
db.books.drop()
db.books.insertMany(
[
{
_id: 1,
user: "1234",
stop: { title: "book1", author: "xyz", page: 32 }
},
{
_id: 2,
user: "7890",
stop: [
{ title: "book2", author: "abc", page: 5 },
{ title: "book3", author: "ijk", page: 100 }
]
}
]
)
以下 $project 阶段仅包含嵌入式文档中的 title 字段：
db.books.aggregate( [
{ $project: { "stop.title": 1 } }
] )
// 1
{
"_id": 1,
"stop": {
"title": "book1"
}
}
// 2
{
"_id": 2,
"stop": [
{
"title": "book2"
},
{
"title": "book3"
}
]
}
7. 添加新字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5
}
)
以下 $project 阶段添加新字段 isbn、lastName 和 copiesSold：
db.books.aggregate(
[
{
$project: {
title: 1,
isbn: {
prefix: { $substr: [ "$isbn", 0, 3 ] },
group: { $substr: [ "$isbn", 3, 2 ] },
publisher: { $substr: [ "$isbn", 5, 4 ] },
title: { $substr: [ "$isbn", 9, 3 ] },
checkDigit: { $substr: [ "$isbn", 12, 1] }
},
lastName: "$author.last",
copiesSold: "$copies"
}
}
]
)
{
"_id": 1,
"title": "abc123",
"isbn": {
"prefix": "000",
"group": "11",
"publisher": "2222",
"title": "333",
"checkDigit": "4"
},
"lastName": "zzz",
"copiesSold": 5
}
8. 重命名字段
构造测试数据：
db.books.drop()
db.books.insertOne(
{
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5
}
)
以下 $project 阶段将字段 copies重命名为 copiesSold ：
db.books.aggregate(
[
{
$project: {
title: 1,
copiesSold: "$copies"
}
}
]
)
{
"_id": 1,
"title": "abc123",
"copiesSold": 5
}
SpringBoot 整合MongoDB实现：
// 输入文档
@Data
@Document(collection = "books")
public class Book {
private int _id;
private String title;
private String isbn;
private Author author;
private int copies;
private String lastModified;
@Data
public static class Author {
private String last;
private String first;
}
}
// 输出文档
@Data
public class AggregationResult {
private int _id;
private String title;
private String isbn;
private Author author;
private int copiesSold;
private String lastModified;
@Data
public static class Author {
private String last;
private String first;
}
}
// 聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {
@Autowired
private MongoTemplate mongoTemplate;
@Test
public void testAggregate(){
// project 阶段
ProjectionOperation project = Aggregation.project("title").and("copies").as("copiesSold");
// 组合阶段
Aggregation aggregation = Aggregation.newAggregation(project);
// 执行聚合
AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Book.class, AggregationResult.class);
List<AggregationResult> mappedResults = aggregationResults.getMappedResults();
// 打印结果
mappedResults.forEach(System.out::println);
// AggregationResult(_id=1, title=abc123, isbn=null, author=null, copiesSold=5, lastModified=null)
}
}
原文地址：https://blog.csdn.net/qq_42764468/article/details/140701238
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！