需求

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>

接口实现代码如下：

    @Override
    public 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;
    }