mongodb 多表关联查询

需求

mongodb库中有三张表，需求：查询表A中的数据，但这些数据的threadId字段，不能存在于表B和表C中，做一个过滤查询，需要分页和排序。

mongodb 命令实现

{"aggregate": "__collection__","pipeline": [{"$match": {"open": 1,"closed": 0}},{"$lookup": {"from": "B","localField": "threadId","foreignField": "threadId","as": "annotation"}},{"$unwind": {"path": "$annotation","preserveNullAndEmptyArrays": true}},{"$match": {"$or": [{"annotation": null},{"annotation.reBacked": 1}]}},{"$lookup": {"from": "C","localField": "threadId","foreignField": "threadId","as": "queue"}},{"$unwind": {"path": "$queue","preserveNullAndEmptyArrays": true}},{"$match": {"queue": null}},{"$sort": {"createTime": -1}},{"$skip": 0},{"$limit": 10}]
}

其实就是多个$lookup的累加，需要配合$unwind使用。

java springboot 实现

pom.xml文件依赖

        <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-mongodb</artifactId></dependency>

接口实现代码如下：

    @Overridepublic List<ThreadEntity> getThreadList(ThreadVo threadVo) throws Exception {String source = threadVo.getSource();Integer page = threadVo.getPage();Integer pageSize = threadVo.getPageSize();Integer type = threadVo.getType();LookupOperation lookupOperation1 = LookupOperation.newLookup().from("B").localField("threadId").foreignField("threadId").as("annotation");LookupOperation lookupOperation2 = LookupOperation.newLookup().from("C").localField("threadId").foreignField("threadId").as("queue");UnwindOperation unwind1 = UnwindOperation.newUnwind().path("annotation").noArrayIndex().preserveNullAndEmptyArrays();UnwindOperation unwind2 = UnwindOperation.newUnwind().path("queue").noArrayIndex().preserveNullAndEmptyArrays();Criteria criteria = Criteria.where("open").is(1).and("closed").is(0);AggregationOperation match = Aggregation.match(criteria);Criteria criteria1 = new Criteria();criteria1.orOperator(Criteria.where("annotation").is(null),Criteria.where("annotation.reBacked").is(1));Criteria criteria2 = new Criteria().where("queue").is(null);AggregationOperation match1 = Aggregation.match(criteria1);AggregationOperation match2 = Aggregation.match(criteria2);AggregationOperation sort = Aggregation.sort(Sort.by(Sort.Order.desc(annotationLabelService.getGetConntentType(type))));AggregationOperation skip = Aggregation.skip((page - 1) * pageSize);AggregationOperation limit = Aggregation.limit(pageSize);Aggregation aggregation = Aggregation.newAggregation(match, lookupOperation1, unwind1, match1, lookupOperation2, unwind2, match2, sort, skip, limit);List<ThreadEntity> list = (List<ThreadEntity>) mongoUtils.lookUp(aggregation, "A", ThreadEntity.class);return list;}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！