关联操作
Criteria criteria = new Criteria();
criteria.and("status").ne(GlobalStatus.STATUS_DELETE.value());
if (StrUtil.isNotBlank(keyword)) {
criteria.and("customer").regex(keyword);
}
Aggregation aggregation = Aggregation.newAggregation(
// 查询条件
match(criteria),
// 排序（可多个）
Aggregation.sort(Sort.Direction.DESC, "_id"),
// 跳过多少条
Aggregation.skip((pageIndex - 1) * pageSize),
// 查询多少条数据
limit(pageSize),
// 关联其他表（本表字段关联其他表的主键）
lookup(UserShowInfoView.collectionName, "uid", "_id", "user0"),
// 关联其他表（本表主键关联其他表的字段，最好给关联表的关联字段加索引，加快查询速度）
lookup(ShopServer.COLLECTION_NAME, "_id", "shopId", "server0"),
// 子表再次关联其他表（注意尽量设计表中带字段，该操作性能不高）
lookup(User.COLLECTION_NAME, "$server0.uid", "_id", "user1"),
// 数据处理，字段过多使用Class工具类
project("applyStatus", "violationTag", "status", "authV", "avatar","name")
// 更改字段名进行映射
.and("schoolName").as("name")
// 取关联数据的第一条数据
.andExpression("arrayElemAt('$user0',0)").as("user")
// 取关联数据的第一条的某个字段
.andExpression("arrayElemAt('$authV0.authV',0)").as("authV")
// 取关联数据的条数（数量）
.andExpression("{$size:'$server0'}").as("serverNum")
// 加减（减-1也可）
.and("evaluationCount").plus(1).as("evaluationCount")
// 除法计算
.andExpression("{$divide:{'$goodEvaluationCount','$evaluationCount'}}").as("favourable")
// 给null的数据赋默认值
.and("isTopping").applyCondition(ConditionalOperators.IfNull.ifNull("isTopping").then("0"))
// 是否存在（用于是否收藏、点赞）
.andExpression(String.format("in(new org.bson.types.ObjectId('%s'),msgLike.uid)", uid)).as("like")
);
List<ShopAdminListVo> list = mongoTemplate.aggregate(aggregation, Shop.COLLECTION_NAME, ShopAdminListVo.class).getMappedResults();
注意：关联后的变量名称不要使用status，会报错
关联分组
Aggregation.group("uid").count().as("count")
.first("uid").as("uid")
.first("lawDeptId").as("lawDeptId")
Aggregation.group().sum("time").as("count")
注意：group后就剩下_id字段，且为group中的字段值，如果后面需要其他字段使用first
关联排序
// 单条件单字段
Aggregation.sort(Sort.Direction.DESC, "createDate")
// 单条件多字段
Aggregation.sort(Sort.Direction.DESC, "status", "createDate")
// 多条件
Aggregation.sort(Sort.Direction.ASC, "status").and(Sort.Direction.DESC, "createDate")
sort(new Sort(Sort.Order.asc("topType"), Sort.Order.desc("sort"), Sort.Order.desc("topDate"), Sort.Order.desc("_id"))),
// 多条件格式
Sort sort;
if (dateSort == 0) {
sort = new Sort(Sort.Direction.DESC, "createDate");
} else {
sort = new Sort(Sort.Direction.ASC, "createDate");
}
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(criteria),
Aggregation.sort(sort)
);
关联过滤
//过滤多条件：filter
BasicDBObject filter4 = new BasicDBObject("$filter", new BasicDBObject(
"input","$pushMessage0"
).append("as", "item")
.append("cond", new BasicDBObject("$and",
new Object[] {
new BasicDBObject("$eq", new Object[] {"$$item.classify", Constant.INTERACTION_NOTIFY_CLASSIFY}),
new BasicDBObject("$eq", new Object[] {"$$item.status", Constant.COMM_STATUS_NORMAL})
}))
);
BasicDBObject reserveFilter = BasicDBObject.parse("{'$filter':{'input':'$pushMessage0','as':'item','cond':{'$and':[{'$eq':['$$item.classify',4]},{'$eq':['$$item.status',0]}]}}}");
//过滤单条件：filter
BasicDBObject filter = new BasicDBObject("$filter", new BasicDBObject(
"input","$lives0"
).append("as", "item")
.append("cond", new BasicDBObject("$eq",
new Object[] {"$$item.status", Constant.COMM_STATUS_NORMAL})));
BasicDBObject reserveFilter = BasicDBObject.parse("{'$filter':{'input':'$reserve0','as':'item','cond':{'$eq':['$$item.status',0]}}}");
Aggregation agg = newAggregation(
geoNear(NearQuery.near(new Point(lon, lat))
.query(query(where("_id").is(id)))
.distanceMultiplier(Constant.EarthRadius)
.spherical(true), "dist.dis"),//距离geoNear查询
lookup(Camera.collectionName, "live", "_id", "lives0"), //关联查询
lookup(UserShowInfoView.collectionName, "uid", "_id", "user0"),
lookup(LikeRecodeView.collectionName, "_id", "oid", "like0"),
lookup(BusinessWantGo.collectionName, "_id", "bid", "wantGo0"),
lookup(BusinessCustomerRecord.collectionName, "_id", "bid", "customerRecode0"),
Aggregation.graphLookup(LegalVideoType.COLLECTION_NAME)
.startWith("$_id")
.connectFrom("_id")
.connectTo("parentId").as("son"),
project("status", "hot", "uid", "name", "keyWordType", "intro", "intro2"
, "cover", "loc", "address", "doBusinessWeek", "startDoBusinessTime"
, "endDoBusinessTime", "telphone", "createDate")
.andExpression("dist.dis").as("dis")
.andExpression("substr('$cityCode', 4, 2)").as("sotCityCode1")//截取字符串，从某（4）位置开始截取几（2）位；
.and(new BaseAggregateExpression(filter)).as("live") //过滤器过来数组
.andExpression("arrayElemAt(\"$user0\",0)").as("user") //选取数组第0个
.andExpression(String.format("in(new org.bson.types.ObjectId(\"%s\"),like0.uid)", uid)).as("like") //判断是否包含
.andExpression("{$size:'$like0'}").as("likeNum") //统计数组长度
.andExpression("{$size:'$wantGo0'}").as("wantGoNum")
.andExpression("{$size:'$customerRecode0'}").as("customerRecodeNum")
.andExpression("{$reverseArray:'$customerRecode0'}").as("customerRecode1"),//数组翻转
project("status", "hot", "uid", "name", "keyWordType", "intro", "intro2"
, "cover", "loc", "address", "doBusinessWeek", "startDoBusinessTime"
, "endDoBusinessTime", "telphone", "createDate"
, "live", "user", "like", "likeNum", "wantGoNum"
, "customerRecodeNum", "dis")
.andExpression(String.format("{slice(customerRecode1,%s)}", Constant.LIST_LIMIT_5)) //截取数组指定长度
.as("customerRecode2"),
lookup(UserShowInfoView.collectionName, "customerRecode2.uid", "_id", "callUsers"),
project("status", "hot", "uid", "name", "keyWordType", "intro", "intro2"
, "cover", "loc", "address", "doBusinessWeek", "startDoBusinessTime"
, "endDoBusinessTime", "telphone", "createDate"
, "live", "user", "like", "likeNum", "wantGoNum"
, "customerRecodeNum", "callUsers", "dis")
);
BasicDBObject obj = mongoTemplate.aggregate(agg, Business.class, BasicDBObject.class).getUniqueMappedResult();
字段计算
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(Criteria.where("type").in(1, 3).and("status").is(0)),
Aggregation.project("startTime", "endTime").andExpression("endTime - startTime").as("totalTime"),
Aggregation.group().sum("totalTime").as("totalTime")
);
层级遍历
例如多级分类等场景
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(criteria1),
Aggregation.graphLookup(LegalVideoType.COLLECTION_NAME)
.startWith("$_id")
.connectFrom("_id")
.connectTo("parentId").as("son"),
Aggregation.project().andExpression("son._id").as("sonIdList")
);
List<Document> dbObjectList = mongoTemplate.aggregate(aggregation, LegalVideoType.COLLECTION_NAME, Document.class).getMappedResults();
注意
集合查询中如果出现关联查询，最好为关联表的外键建立索引，否则数据量大了就会比较慢
