document);
$lookup 操作符
Performs a left outer join to a collection in the same database to filter in documents from the "joined" collection for processing. The $lookup stage adds a new array field to each input document. The new array field contains the matching documents from the "joined" collection.
The $lookup stage passes these reshaped documents to the next stage.
Starting in MongoDB 5.1, $lookup works across sharded collections.
其实可以简单理解类比Mysql的子查询。 会把另外一张表匹配的数据,作为一个数组存入到当前数据中,需要自定义一个字段来接收显示。
类比如下的sql语句
SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
SELECT *
FROM <collection to join>
WHERE <foreignField> = <collection.localField>
【特别注意】如果当前DB是集群部署,那么在DB版本为5.1之前的情况,$lookup是不会生效的。 如果你数据库是集群的,然后又要用$lookup,一定要检查版本是否大于等于5.1,否则是查不出来的。 前阵子不知道这个,一直没有头绪为什么数据查不出来。
$lookup:
from: <collection to join>, // 要联表查的表名
localField: <field from the input documents>, // 当前表的要和联表关联的字段
foreignField: <field from the documents of the "from" collection>, // 要被关联表的外键字段
as: <output array field> // 定义一个字段接收匹配关联的数据
在MongoDB中操作的官方示例
// 插入表orders数据
db.orders.insertMany( [
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 }
// 插入表inventory数据
db.inventory.insertMany( [
{ "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, "description": "Incomplete" },
{ "_id" : 6 }
db.orders.aggregate( [ // db.orders 表示基于orders做聚合操作
$lookup:
from: "inventory", // 联表inventory
localField: "item", // 当前orders的字段
foreignField: "sku", // inventory中的sku字段,和orders的item关联
as: "inventory_docs" // 定义一个字段名接收 inventory中sku 和orders的item相同的数据,数组形式。
"_id" : 1,
"item" : "almonds",
"price" : 12,
"quantity" : 2,
"inventory_docs" : [ // 这个inventory_docs字段就是自己命名的字段,存储着来自inventory的数据
{ "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
"_id" : 2,
"item" : "pecans",
"price" : 20,
"quantity" : 1,
"inventory_docs" : [
{ "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
"_id" : 3,
"inventory_docs" : [
{ "_id" : 5, "sku" : null, "description" : "Incomplete" },
{ "_id" : 6 }
MongoTemplate中实现的Java代码
以下分别是Aggregation和Aggregates的实现
* @Author zaoyu
* Aggregation 实现$lookup
@Test
public void testLookupAggregations(){
String INVENTORY_COLLECTION = "inventory";
String ORDERS_COLLECTION = "orders";
// Aggregation类,直接可以调用lookup方法,传入要关联的表、当前表和关联表关联的字段、要关联的表的字段、自定义名称接收关联匹配的数据
LookupOperation lookup = Aggregation.lookup(INVENTORY_COLLECTION, "item", "sku", "inventory_docs");
// 用newAggregation接收管道聚合指令,执行,得到结果。
Aggregation aggregations =Aggregation.newAggregation(lookup);
// mongoTemplate 直接调用aggregate方法,传入Aggregation对象,基于的表,映射类(这里简单化,我用Document)
AggregationResults<Document> resultList = mongoTemplate.aggregate(aggregations, ORDERS_COLLECTION, Document.class);
for (Document document : resultList) {
System.out.println("result is :" + document);
* @Author zaoyu
* Aggregates/Bson 实现$lookup
@Test
public void testLookupAggregates(){
String INVENTORY_COLLECTION = "inventory";
String ORDERS_COLLECTION = "orders";
// 这里用Aggregates类直接调用lookup,传入的参数和上面的Aggregations的lookup是一样的,只不过这里返回的结果是一个Bson对象。
Bson lookupBson = Aggregates.lookup(INVENTORY_COLLECTION, "item", "sku", "inventory_docs");
// 建一个List<Bson> 把lookupBson传进去
List<Bson> bsonList = new ArrayList<>();
bsonList.add(lookupBson);
// mongoTemplate先获得对应的collection对象,然后调用aggregate,传入List<Bson> 获得结果
MongoCollection<Document> collection = mongoTemplate.getCollection(ORDERS_COLLECTION);
AggregateIterable<Document> resultList = collection.aggregate(bsonList);
for (Document document : resultList) {
System.out.println("result is :" + document);
result is :Document{{_id=1.0, item=almonds, price=12.0, quantity=2.0, inventory_docs=[Document{{_id=1.0, sku=almonds, description=product 1, instock=120.0}}]}}
result is :Document{{_id=2.0, item=pecans, price=20.0, quantity=1.0, inventory_docs=[Document{{_id=4.0, sku=pecans, description=product 4, instock=70.0}}]}}
result is :Document{{_id=3.0, inventory_docs=[Document{{_id=5.0, sku=null, description=Incomplete}}, Document{{_id=6.0}}]}}
$facet 操作符
Processes multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its results are stored as an array of documents.
Input documents are passed to the $facet stage only once. $facet enables various aggregations on the same set of input documents, without needing to retrieve the input documents multiple times.
简单来说,就是facet可以实现在facet管道操作完成多个stage管道操作。减少获取输入文档的次数。
我个人觉得有种场景很适合使用facet:分页查询文档数据的同时,把符合查询条件的总数也查询出来的场景下,如果使用$facet,同时获取分页数据和总数,不用做两次数据库查询(分别查询分页数据和总数)。
{ $facet:
<outputField1>: [ <stage1>, <stage2>, ... ], // 这里outputpufield 是自己定义的用来接收stage集合返回的文档数据。
<outputField2>: [ <stage1>, <stage2>, ... ], // 可以基于上一个Facet继续做facet
在MongoDB中操作的官方示例
// 数据 插入artwork 表中
{ "_id" : 1, "title" : "The Pillars of Society", "artist" : "Grosz", "year" : 1926,
"price" : NumberDecimal("199.99"),
"tags" : [ "painting", "satire", "Expressionism", "caricature" ] }
{ "_id" : 2, "title" : "Melancholy III", "artist" : "Munch", "year" : 1902,
"price" : NumberDecimal("280.00"),
"tags" : [ "woodcut", "Expressionism" ] }
{ "_id" : 3, "title" : "Dancer", "artist" : "Miro", "year" : 1925,
"price" : NumberDecimal("76.04"),
"tags" : [ "oil", "Surrealism", "painting" ] }
{ "_id" : 4, "title" : "The Great Wave off Kanagawa", "artist" : "Hokusai",
"price" : NumberDecimal("167.30"),
"tags" : [ "woodblock", "ukiyo-e" ] }
{ "_id" : 5, "title" : "The Persistence of Memory", "artist" : "Dali", "year" : 1931,
"price" : NumberDecimal("483.00"),
"tags" : [ "Surrealism", "painting", "oil" ] }
{ "_id" : 6, "title" : "Composition VII", "artist" : "Kandinsky", "year" : 1913,
"price" : NumberDecimal("385.00"),
"tags" : [ "oil", "painting", "abstract" ] }
{ "_id" : 7, "title" : "The Scream", "artist" : "Munch", "year" : 1893,
"tags" : [ "Expressionism", "painting", "oil" ] }
{ "_id" : 8, "title" : "Blue Flower", "artist" : "O'Keefe", "year" : 1918,
"price" : NumberDecimal("118.42"),
"tags" : [ "abstract", "painting" ] }
// 执行$facet聚合
db.artwork.aggregate( [
$facet: {
// 第一个Facet操作,按照tag分类:先用unwind拆分tags字段的数组值,交给下一个聚合 $sortByCount, 按照tags的个数排序。
"categorizedByTags": [
{ $unwind: "$tags" },
{ $sortByCount: "$tags" }
// 第二个Facet操作,按照price分类:先过滤数据(只处理存在price数据的文档),然后执行$bucket按照价格区间分组 0~150,151~200, 201~300, 301~400这样。
"categorizedByPrice": [
{ $match: { price: { $exists: 1 } } },
$bucket: {
groupBy: "$price",
boundaries: [ 0, 150, 200, 300, 400 ],
default: "Other",
output: {
"count": { $sum: 1 },
"titles": { $push: "$title" }
// 第三个Facet, 按照years分类。 分成4个区间。
"categorizedByYears(Auto)": [
$bucketAuto: {
groupBy: "$year",
buckets: 4
MongoTemplate中实现的Java代码
注:以下代码的实现,数据来源参考上边的官方示例的数据, artwork表。 请自行插入数据。
1. 使用Aggregation对象实现
* @Author zaoyu
* Aggregation 实现$facet
@Test
public void testFacetAggregations(){
String ARTWORK_COLLECTION = "artwork";
// Facet中第一组分类(categorizedByTags)的两个聚合操作unwind 和 sortByCount
UnwindOperation unwindForByTags = Aggregation.unwind("$tags");
SortByCountOperation sortByCountForByTags = Aggregation.sortByCount("$tags");
// Facet中第二组分类(categorizedByPrice)的聚合操作match 和 match
MatchOperation matchForByPrice = Aggregation.match(Criteria.where("price").exists(true));
// 分别传入bucket分组的字段price,设置区间值,并设置桶内条数统计和值(这里用titles接收title的值)
BucketOperation bucketForByPrice = Aggregation.bucket("$price")
.withBoundaries(0, 150, 200, 300, 400)
.withDefaultBucket("Other")
.andOutput("count").sum(1).as("count")
.andOutput("$title").push().as("titles");
// Facet中第三组分类 (categorizedByYears(Auto))的聚合操作,按年自动分成4个区间。
BucketAutoOperation bucketForByYears = Aggregation.bucketAuto("$year", 4);
// Aggregation调用facet方法,按照组别分类顺序,把每一组的聚合操作和输出的名称传进去。
FacetOperation facetOperation = Aggregation.facet(unwindForByTags, sortByCountForByTags).as("categorizedByTags")
.and(matchForByPrice, bucketForByPrice).as("categorizedByPrice")
.and(bucketForByYears).as("categorizedByYears(Auto)");
// 把facetOperation传入newAggregation得到Aggregation对象,调用mongoTemplate的Aggregate方法执行得到结果
Aggregation aggregation = Aggregation.newAggregation(facetOperation);
AggregationResults<Document> resultList = mongoTemplate.aggregate(aggregation, ARTWORK_COLLECTION, Document.class);
for (Document document : resultList) {
System.out.println("result is :" + document);
2. 使用Aggregates实现
* @Author zaoyu
* Aggregates 实现$facet
@Test
public void testFacetAggregates() {
String ARTWORK_COLLECTION = "artwork";
// Facet中第一组分类(categorizedByTags)的两个聚合操作unwind 和 sortByCount
Bson unwindBsonForByTags = Aggregates.unwind("$tags");
Bson sortByCountBsonForByTags = Aggregates.sortByCount("$tags");
// 新建Facet对象,传入第一组分类的接收名称,以及在第一组分类中要做的聚合操作。
Facet categorizedByTags = new Facet("categorizedByTags", unwindBsonForByTags, sortByCountBsonForByTags);
// Facet中第二组分类(categorizedByPrice)的聚合操作match 和 match
Bson matchBsonForPrice = Aggregates.match(Filters.exists("price"));
// 这里面要新建BsonField构建 {"count": { $sum: 1 } 和 "titles": { $push: "$title" }} 作为第二组分类中$Bucket聚合操作中output值
BsonField countOutput = new BsonField("count", new Document("$sum", 1));
BsonField titleOutput = new BsonField("titles", new Document("$push", "$price"));
// 上面2个操作传入到BucketOption对象,最后传到bucket操作
BucketOptions bucketOptions = new BucketOptions().defaultBucket("Other").output(countOutput).output(titleOutput);
Bson bucketBsonForByPrice = Aggregates.bucket("$price", Arrays.asList(0, 150, 200, 300, 400), bucketOptions);
Facet categorizedByPrice = new Facet("categorizedByPrice", matchBsonForPrice, bucketBsonForByPrice);
// Facet中第三组分类 (categorizedByYears(Auto))的聚合操作,按年自动分成4个区间。
Bson bucketAutoBsonForByYears = Aggregates.bucketAuto("$year", 4);
Facet categorizedByYears = new Facet("categorizedByYears", bucketAutoBsonForByYears);
// 新建一个List<Facet>把每组分类的Facet对象传进去。
List<Facet> facetList = new ArrayList<>();
facetList.add(categorizedByTags);
facetList.add(categorizedByPrice);
facetList.add(categorizedByYears);
// 调用Aggregates的facet方法,传入List<Facet>得到最终Bson对象,并添加到Bson集合中。
Bson facetBson = Aggregates.facet(facetList);
List<Bson> bsonList = new ArrayList<>();
bsonList.add(facetBson);
// 调用方法执行得到结果
MongoCollection<Document> collection = mongoTemplate.getCollection(ARTWORK_COLLECTION);
AggregateIterable<Document> resultList = collection.aggregate(bsonList);
for (Document document : resultList) {
System.out.println("result is :" + document);
最终返回结果, 二者一样。
result is :Document{{categorizedByTags=[Document{{_id=painting, count=6}}, Document{{_id=oil, count=4}}, Document{{_id=Expressionism, count=3}}, Document{{_id=Surrealism, count=2}}, Document{{_id=abstract, count=2}}, Document{{_id=woodblock, count=1}}, Document{{_id=ukiyo-e, count=1}}, Document{{_id=satire, count=1}}, Document{{_id=caricature, count=1}}, Document{{_id=woodcut, count=1}}], categorizedByPrice=[Document{{_id=0, titles=[76.04, 118.42]}}, Document{{_id=150, titles=[199.99, 167.30]}}, Document{{_id=200, titles=[280.00]}}, Document{{_id=300, titles=[385.00]}}, Document{{_id=Other, titles=[483.00]}}], categorizedByYears=[Document{{_id=Document{{min=null, max=1902.0}}, count=2}}, Document{{_id=Document{{min=1902.0, max=1918.0}}, count=2}}, Document{{_id=Document{{min=1918.0, max=1926.0}}, count=2}}, Document{{_id=Document{{min=1926.0, max=1931.0}}, count=2}}]}}
整体来说,MonogoDB官方提供了很详细的资料,但是对于Java 层面的操作,或者说SpringBoot层面的操作,文档就比较简单。
个人感觉而言,Aggregations提供的方法比较直接,更适合不太熟悉Springboot上操作Mongo的同学来使用,而Aggregates会更加灵活,但是需要你知道Document, BsonField, Bson之间的转换和获取。
希望这篇文章能帮到大家,有错漏之处,欢迎指正。