Mongodb 关联表查询

前言

最近学习使用mongodb中间使用关联表查询问题遇到一些问题记录下来分享给大家。

mongodb关联查询

之前使用SQL语法来查询oracle、sqlserver、mysql表之间的关联，但是到mongodb之后完全无从下手，写法完全不一样，于是到网上查询mongodb关联表查询的写法，于是参考代码自己试着写了下，但是发现有好多问题，比如我有两个表user和apple，

user表：

id	name	age
ObjectId(1xxxx1)	zhangsan	32
ObjectId(2xxxx2)	lishi	23
ObjectId(3xxxx3)	wangwu	19
ObjectId(4xxxx4)	liuliu	28

apple表：

id	uid	device
ObjectId(axxxxa)	1xxxx1	10
ObjectId(bxxxxb)	2xxxx2	2
ObjectId(cxxxxc)	3xxxx3	7
ObjectId(dxxxxd)	4xxxx4	1

user实体类：

@Setter
@Getter
public class User {
    private String id;
    private String name;
    private Integer age;
    private Date createDt;
}

apple实体类：

@Setter
@Getter
public class Apple {
    private String id;
    private String uid;
    private Integer device;
    private Date createDt;
}

user和apple之间是一对一关系，现在我的查询要求是：按device数量从大到小排序查询用户数据。

查询结果应该是：

id	name	age
ObjectId(1xxxx1)	zhangsan	32
ObjectId(3xxxx3)	wangwu	19
ObjectId(2xxxx2)	lishi	23
ObjectId(4xxxx4)	liuliu	28

我们分别使用user表和apple表做主表来查询，那么使用mongodb语法我们试着来写一下。

以user表做主表查询

// 1、添加_id字段类型转换
AddFieldsOperation addFieldsOperation = AddFieldsOperation.addField("_id").withValue(ConvertOperators.ToString.toString("$_id")).build();

// 2、按参数顺序：被关联表apple，主表user.id，被关联表apple.uid，别名
LookupOperation lookupOperation = Aggregation.lookup("apple", "_id", "uid", "apple_as");

// 3、查询哪些字段，类似sql里面的 select 选择器
ProjectionOperation project = Aggregation.project("name","age","createDt").and("apple_as.device").as("device");

// 4、按照apple.device数量排序
// 注意：不能使用apple_as.device，并且 device字段必须出现在project里面，否则查询失败
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "device");

// 5、添加取List中
List<AggregationOperation> operationList = Lists.newArrayList();
operationList.add(addFieldsOperation);
operationList.add(lookupOperation);
operationList.add(project);
operationList.add(sort);
Aggregation agg = Aggregation.newAggregation(operationList);

// 因为返回的是User实体类，所以不会出现device字段，你可以使用Map.class来返回想要的字段
AggregationResults<User> aggregationResults = mongoTemplate.aggregate(agg, "user", User.class);
// 6、返回关联查询结果
List<User> dataList = aggregationResults.getMappedResults();

注意：我们在查询之前要把主表的id转换成String类型，因为被关联表的uid就是String类型，否则查询失败

以apple表做主表查询

// 1、添加uid字段类型转换
AddFieldsOperation addFieldsOperation = AddFieldsOperation.addField("uid").withValue(ConvertOperators.ToObjectId.toObjectId("$uid")).build();

// 2、按参数顺序：被关联表user，主表apple.uid，被关联表user.id，别名
LookupOperation lookupOperation = Aggregation.lookup("user", "uid", "_id", "user_as");

// 3、查询哪些字段，类似sql里面的 select 选择器
// 注意：因为user是被关联表，所以要使用user_as别名的方式来获取用户信息字段
ProjectionOperation project = Aggregation.project("user_as.name","user_as.age","user_as.createDt","device");

// 4、按照apple.device数量排序
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "device");

// 5、添加取List中
List<AggregationOperation> operationList = Lists.newArrayList();
operationList.add(addFieldsOperation);
operationList.add(lookupOperation);
operationList.add(project);
operationList.add(sort);
Aggregation agg = Aggregation.newAggregation(operationList);

// 因为返回的是User实体类，所以不会出现device字段，你可以使用Map.class来返回想要的字段
AggregationResults<User> aggregationResults = mongoTemplate.aggregate(agg, "apple", User.class);
// 6、返回关联查询结果
List<User> dataList = aggregationResults.getMappedResults();

注意：我们在查询之前要把主表的uid转换ObjectId类型，因为被关联表的_id就是ObjectId类型，否则查询失败

2022-01-14更新

三表关联

List<AggregationOperation> operationList = Lists.newArrayList();
AddFieldsOperation add1FieldsOperation = AddFieldsOperation.addField("categoryId").withValue(ConvertOperators.ToObjectId.toObjectId("$categoryId")).build();

LookupOperation lookupOperation1 = Aggregation.lookup("urlCategory", "categoryId", "_id", "category_as");
LookupOperation lookupOperation2 = Aggregation.lookup("user", "uid", "uid", "user_as");

MatchOperation match = Aggregation.match(Criteria.where("uid").in(uids).and("isDelete").is(false));
ProjectionOperation project = Aggregation.project("user_as.userName","user_as.avatar","uid", "title", "url", "domain", "favicon", "isAttention", "isCopy", "createdDt")
        .and("category_as.name").as("categoryName")
        .andExpression("{ $dateToString: {date: '$createdDt', format: '%Y-%m-%d %H:%M:%S', timezone: '+08:00'}}").as("date")
        .and(ConvertOperators.Convert.convertValueOf("categoryId").to("string")).as("category_id");
// sort里面的clickCount字段必须从Project中取，否则 失败
SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "createdDt");
FacetOperation facet = Aggregation.facet(
        count().as("count")).as("total")
        .and(skip(pageSize * (pageNum - 1)), limit(pageSize)).as("rows");
UnwindOperation unwindOperation1 = Aggregation.unwind("category_as");
UnwindOperation unwindOperation2 = Aggregation.unwind("user_as");
operationList.add(add1FieldsOperation);
operationList.add(lookupOperation1);
operationList.add(lookupOperation2);
operationList.add(unwindOperation1);
operationList.add(unwindOperation2);
//因为match里面含有url_as，所以lookupOperation要在match上面
operationList.add(match);
operationList.add(project);
operationList.add(sort);
operationList.add(facet);

Aggregation agg = Aggregation.newAggregation(operationList);
// collectionName:主表
AggregationResults<HashMap> aggregationResults = mongoTemplate.aggregate(agg, "url", HashMap.class);
return aggregationResults.getMappedResults();

2022-12-22更新

三表关联一对多(多的这边进行条件筛选)

 // 1、添加uid字段类型转换
        AddFieldsOperation addFieldsOperation1 = AddFieldsOperation.addField("objectId").withValue(ConvertOperators.ToObjectId.toObjectId("$objectId")).build();
        AddFieldsOperation addFieldsOperation2 = AddFieldsOperation.addField("objectId").withValue(ConvertOperators.ToString.toString("$objectId")).build();
        // 2、按参数顺序：被关联表url，主表userEvent.objectId，被关联表url.id，别名
        LookupOperation lookupOperation1 = Aggregation.lookup("url", "objectId", "_id", "url_as");
        LookupOperation lookupOperation2 = Aggregation.lookup("userLikeUrl", "objectId", "urlId", "userLikeUrl_as");
        LookupOperation lookupOperation3 = Aggregation.lookup("userCopyUrl", "objectId", "fromUrlId", "userCopyUrl_as");

        MatchOperation matchOperation1 = Aggregation.match(Criteria.where("url_as.uid").in(uids).and("url_as.isDelete").is(false));

        ProjectionOperation projectionOperation1 = Aggregation.project("uid", "userName", "userAvatar", "action", "objectType", "objectOthers",
                "createdDt", "url_as", "userCopyUrl_as")
                .andExpression("toString(objectId)").as("objectId")
                // 先筛选出userLikeUrl_as.valid=1的列表到userLikeUrl_as_list中
                .and(filter("userLikeUrl_as").as("item")
                        .by(ComparisonOperators.Eq.valueOf("item.valid").equalToValue(1))).as("userLikeUrl_as_list");

        ProjectionOperation projectionOperation2 = Aggregation.project("uid", "userName", "userAvatar", "action", "objectType", "objectOthers",
                "createdDt", "url_as", "objectId", "userCopyUrl_as")
                // 再对userLikeUrl_as_list统计数量到userLikeUrl_count
                .andExpression("{$size: '$userLikeUrl_as_list'}").as("userLikeUrl_count")
                .andExpression("{$size: '$userCopyUrl_as'}").as("userCopyUrl_count");


        // 4、按照apple.device数量排序
        SortOperation sort = Aggregation.sort(Sort.Direction.DESC, "createdDt");

        FacetOperation facet = Aggregation.facet(count().as("count")).as("total").and(skip(pageSize * (pageNum - 1)), limit(pageSize)).as("rows");
        UnwindOperation unwindOperation1 = Aggregation.unwind("url_as");

        List<AggregationOperation> operationList = Lists.newArrayList();
        operationList.add(lookupOperation3);
        operationList.add(lookupOperation2);

        operationList.add(addFieldsOperation1);
        operationList.add(lookupOperation1);
        operationList.add(matchOperation1);

        operationList.add(unwindOperation1);

        operationList.add(projectionOperation1);
        operationList.add(projectionOperation2);

        operationList.add(sort);
        operationList.add(facet);
        Aggregation agg = Aggregation.newAggregation(operationList);
        // 因为返回的是User实体类，所以不会出现device字段，你可以使用Map.class来返回想要的字段
        AggregationResults<HashMap> aggregationResults = mongoTemplate.aggregate(agg, "userEvent", HashMap.class);
        return aggregationResults.getMappedResults();

注意看上面的projectionOperation1和projectionOperation2，并且lookupOperation2中有userLikeUrl_as集合数据，然后我们在projectionOperation1中添加筛选条件过滤出userLikeUrl_as_list列表，如下所示：

.and(filter("userLikeUrl_as").as("item")
                        .by(ComparisonOperators.Eq.valueOf("item.valid").equalToValue(1))).as("userLikeUrl_as_list");

过滤条件是：item.valid=1的数据，接着我们再projectionOperation2中聚合数据统计userLikeUrl_as_list集合中的数量

 .andExpression("{$size: '$userLikeUrl_as_list'}").as("userLikeUrl_count")

总结

1、mongodb关联查询一定要注意ObjectId类型和String类型之间的转换，否则不成功
ObjectId转换String方法一：

.andExpression("toString(objectId)").as("objectId")

ObjectId转换String方法二：

.and(ConvertOperators.Convert.convertValueOf("categoryId").to("string")).as("category_id")

ObjectId转换String方法三：

AddFieldsOperation addFieldsOperation2 = AddFieldsOperation.addField("objectId").withValue(ConvertOperators.ToString.toString("$objectId")).build();

String转换ObjectId方法一：

AddFieldsOperation addFieldsOperation1 = AddFieldsOperation.addField("objectId").withValue(ConvertOperators.ToObjectId.toObjectId("$objectId")).build();

2、project中出现的字段会影响 sort排序
3、operationList的添加前后顺序也会影响查询
4、如果查询失败要多试几遍，到底是哪一步出现的影响，我也是不断的试错才总结出来的。
5、如果有条件的话可以加如下代码：

AggregationOperation match = Aggregation.match(Criteria.where("uid").is(uid));
LimitOperation limit = Aggregation.limit(10);

operationList.add(match);
operationList.add(limit);