MongoDB复杂关联查询 spring实现

初步版本 没加分页 没进行截取
db.topic_record.aggregate([
	{
	  "$match":{
	  //对应为话题的id
		"taskId" : "5f4638cb2dca5a575181342b"
	  }
	},
	//通过$set 命令 增加新字段将ObjectId转换为string类型用来进行关联查询   topicRecord._id --> article_id （临时）--> topic_comment.recordId 被关联的评论表
	{"$set":{article_id:{"$toString":"$_id"}}},
	  { 
	  "$lookup" : 
	  { 
	  "from" : "topic_comment", 
	  "localField" : "article_id", 
	  "foreignField" : "recordId","as" : "comments"
	  }
	  },
	{ "$unset" : "comments.replies"}
]).pretty();

通过本地进行查询测试发现可以执行这样就可以上demo开搞了

3.编写demo进行调试

方式1.首先尝试用自带api进行编写

这里的坑有好几个：

article_id需要将ObjectId装换为String
lookup语句需要用 as(“comments”)进行赋值
sort skip limit 的执行顺序需要确定好如果顺序写反很容易出错这里不会自动修正执行顺序
通过Aggregation.project 的 exclude 和 addExpression 方法都没有实现去除comments.replies 字段的功能,最后发现unset方法可以
这里返回数据类型TopicRecord中映射的comments列表不能使用@Transient注解，如果添加了会映射不到

问题：在这里使用project命令的时候发现一个问题，如果我只想排除某个字段其他字段默认全部显示，语法使用很麻烦
补充：这里使用project也是出于老版本包中不支持addFileds这种操作然后通过project.addExpression 方法可以进行转换就这样保留了

    @Test
    public void testPay4() { 
        String topicId="5f4638cb2dca5a575181342b";
        PageRequest pageRequest = PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id"));
        //关联信息
        LookupOperation lookupOperation = LookupOperation.newLookup().from(mongoTemplate.getCollectionName(TopicComment.class))
                .localField("article_id").foreignField("recordId").as("comments");

        //计时学习推荐查询
        Aggregation aggregation1 = Aggregation.newAggregation(
                Aggregation.match(Criteria.where("taskId").is(topicId)),
                Aggregation.project("id","content","taskId","studyId","username","nickname","avatar","favour","isMaster","commentCount","createTime","imageUrls","comments").andExpression("{$toString:'$_id'}").as("article_id"),
                lookupOperation,
                Aggregation.sort(pageRequest.getSort()),
                Aggregation.skip(pageRequest.getOffset()),
                Aggregation.limit(pageRequest.getPageSize()),
                UnsetOperation.unset("comments.replies")
//                Aggregation.project("id","content","taskId","studyId","username","nickname","avatar","favour","isMaster","commentCount","createTime","imageUrls","comments").andExpression("$unset")
//                Aggregation.project("id","content","taskId","studyId","username","nickname","avatar","favour","isMaster","commentCount","createTime","imageUrls","comments").andExpression("{ comments: { replies: 0 } }")
                );
        AggregationResults<TopicRecord> aggregate = mongoTemplate.aggregate(aggregation1, TopicRecord.class, TopicRecord.class);

        System.out.println(aggregate);
    }

方式1在项目中由于不能使用UnsetOperation等方法寻找其他方式进行编写最后优化结果如下
最终方案
方式2.

@Test
    public void testPay5() {
        //通过bson语句  使用mongo包里Aggregation原生的写法
        String topicId="5f4638cb2dca5a575181342b";
        PageRequest pageRequest = PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "id"));
        //关联信息
        LookupOperation lookupOperation = LookupOperation.newLookup().from(mongoTemplate.getCollectionName(TopicComment.class))
                .localField("article_id").foreignField("recordId").as("comments");
        AggregationOperation unset = context -> new Document("$unset", "comments.replies");
        AggregationOperation set = context -> new Document("$set", new BasicDBObject("article_id", new BasicDBObject("$toString", "$_id")));
        BasicDBList objects = new BasicDBList();
        objects.add("$comments");
        objects.add(0);
        objects.add(1);
        AggregationOperation set2 = context -> new Document("$set", new BasicDBObject("comments", new BasicDBObject("$slice", objects)));
        Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(Criteria.where("taskId").is(topicId)),
                set,
                lookupOperation,
                Aggregation.sort(pageRequest.getSort()),
                Aggregation.skip(pageRequest.getOffset()),
                Aggregation.limit(pageRequest.getPageSize()),
                set2,
                unset
        );
        AggregationResults<TopicRecord> aggregate1 = mongoTemplate.aggregate(aggregation, TopicRecord.class, TopicRecord.class);

        System.out.println(aggregate1);

    }

其余参考链接/获得灵感：

https://www.it1352.com/1551972.html

https://www.it1352.com/987780.html

https://blog.csdn.net/qq_18769269/article/details/81011193

https://blog.csdn.net/haiyoung/article/details/80865043

https://blog.csdn.net/quietlystaywithyou/article/details/85297104

https://docs.mongodb.com/manual/reference/operator/aggregation/project/#remove-var