MongoDB进阶连表查询

mongo 脚本查询

db.getCollection("collect.template").aggregate([
    {
        // 根据条件查询
        $match: {
            "_id": {
                $in: [ObjectId("6539be1d39de5a565e763a99"), ObjectId("6539dcf838b1be438705c053"), ObjectId("653a235538b1be438705c165")]
            }
        }
    },
    {
        // 从 collect.template.group 表查询 templateId 所关联的数据
        $lookup: {
            from: "collect.template.group",
            localField: "_id",
            foreignField: "templateId",
            as: "templateGroup"
        }
    },
    {
        // 查询结果只保留 name 和 groupIds
        $project: {
            name: 1,
            groupIds: {
                $reduce: {
                    // 使用 $reduce 来将对象数组转换为纯 id 数组
                    input: "$templateGroup",
                    initialValue: [],
                    in: {
                        $concatArrays: ["$$value",["$$this.groupId"]]
                    }
                }
            }
        }
    },
    {
        // 将上述查询结果再次联表 collect.group.field 根据 groupId 查询
        $lookup: {
            from: "collect.group.field",
            localField: "groupIds",
            foreignField: "groupId",
            as: "groupField"
        }
    },
    {
        // 将查询结果中的 groupField 筛选，unit 为”万元“的数据
        $addFields: {
            groupField: {
                $filter: {
                    input: "$groupField",
                    cond: {
                        $eq: ["$$this.unit", "万元"]
                    }
                }
            }
        }
    },
    {
        // 最后保留筛选之后的结果，并且只保留如下几个字段
        $project: {
            name: 1,
            groupIds: 1,
            groupField: {
                $map: {
                    input: "$groupField",
                    in: {
                        "title": "$$this.title",
                        "fieldCode": "$$this.fieldName",
                        "groupId": "$$this.groupId"
                    }
                }
            }
        }
    }
]);

上述 js 脚本对应的 spring mongo Java 代码如下：

  private List<HashMap> getGroupFields() {
    ArrayList<ObjectId> objectIds = Lists.newArrayList(new ObjectId("6539be1d39de5a565e763a99"),
        new ObjectId("6539dcf838b1be438705c053"), new ObjectId("653a235538b1be438705c165"));
    MatchOperation match = Aggregation.match(Criteria.where(CreditConstant.MONGO_ID).in(objectIds));

    LookupOperation lookupTemplateGroup =
        Aggregation.lookup("collect.template.group", "_id", "templateId", "templateGroup");

    AggregationOperation projectToGroupId = context -> {
      Document mapExpression = new Document("$reduce",
          new Document().append("input", "$templateGroup")
              .append("initialValue", new Document("$literal", new ArrayList<>()))
              .append("in", new Document().append("$concatArrays",
                  Arrays.asList("$$value", Collections.singletonList("$$this.groupId")))));
      return new Document("$project",
          new Document().append("name", 1).append("type", 1).append("groupIds", mapExpression));
    };

    AggregationOperation lookupGroupField =
        Aggregation.lookup("collect.group.field", "groupIds", "groupId", "groupField");

    AggregationOperation filterByUnit = context -> new Document("$addFields",
        new Document("groupField",
            new Document("$filter", new Document("input", "$groupField").append("as", "item")
                .append("cond", new Document("$eq", Arrays.asList("$$item.unit", "万元"))))));

    AggregationOperation projectGroupFieldDetails = context -> {
      Document mapExpression = new Document("$map",
          new Document().append("input", "$groupField").append("in",
              new Document().append("title", "$$this.title").append("fieldCode", "$$this.fieldName")
                  .append("groupId", "$$this.groupId")));
      return new Document("$project", new Document().append("name", 1).append("type", 1)
          .append("groupIds", 1).append("groupField", mapExpression));
    };

    Aggregation aggregation = Aggregation.newAggregation(match, lookupTemplateGroup,
        projectToGroupId, lookupGroupField, filterByUnit, projectGroupFieldDetails);
    AggregationResults<HashMap> results =
        mongoTemplate.aggregate(aggregation, CollectTemplate.class, HashMap.class);
    return results.getMappedResults();
  }