2

前言

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

mongodb关联查询

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

user表:

idnameagecreateDt
ObjectId(1xxxx1)zhangsan32
ObjectId(2xxxx2)lishi23
ObjectId(3xxxx3)wangwu19
ObjectId(4xxxx4)liuliu28

apple表:

iduiddevicecreateDt
ObjectId(axxxxa)1xxxx110
ObjectId(bxxxxb)2xxxx22
ObjectId(cxxxxc)3xxxx37
ObjectId(dxxxxd)4xxxx41

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数量从大到小排序查询用户数据。

查询结果应该是:

idnameagecreateDt
ObjectId(1xxxx1)zhangsan32
ObjectId(3xxxx3)wangwu19
ObjectId(2xxxx2)lishi23
ObjectId(4xxxx4)liuliu28

我们分别使用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();

注意看上面的projectionOperation1projectionOperation2,并且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);

2023-05-10更新----分页查询

分页查询的时候返回的数据格式体验好差,优化一下,代码如下所示:

优化前格式:
image.png

PageResults.java

@Data
public class PageResults {
    private Long total;
    private Integer pageNum;
    private Integer pageSize;
    private List<HashMap> rows;
}

mongodb代码:

//如果聚合没有查询到数据metadata返回为null
        FacetOperation facet = Aggregation.facet(count().as("total")).as("metadata")
                .and(skip, limit).as("rows");

        AggregationOperation project2 = project("rows")
                // 这里加了metadata的判断,因为当数据库没数据,或者有数据但是查询为空,那么metadata也返回null,为了当metadata=null时,total返回0,所以做了下面的判断
                .and(ConditionalOperators.when(Criteria.where("metadata").is(null)).then(Collections.singletonList(0)).otherwise("$metadata.total"))
                .as("total")
                .andExclude("_id");


        List<AggregationOperation> operationList = Lists.newArrayList();
        operationList.add(facet);
        operationList.add(project2);
        Aggregation agg = Aggregation.newAggregation(operationList);
        AggregationResults<PageResults> results = mongoTemplate.aggregate(agg, "userEvent", PageResults.class);

        PageResults pageResults = results.getUniqueMappedResult();
        pageResults.setPageNum(pageNum + 1);
        pageResults.setPageSize(pageSize);
        return pageResults;

优化后格式:
image.png

注意:使用aggregationResults.getUniqueMappedResult()代码就不会返回数组,而是返回唯一的对象。

注意

1、unwindOperation操作的时候,被unwindOperation解散的数组会成为对象,并且,如果被解散的对象没有对应关系就不会返回到结果列表中,如:A表和B表,A中有10条数据,B表有10条数据,但是B中关联的A表只有2条,当使用unwindOperation关联查询时,返回的结果只有2条!
2、放到group中的字段,如下面的uid,在返回结果的时候,不是本身的数据,所以如果你想在match中匹配会出现问题,解决办法就是:重新加一个uid,如下所示,或者新增一个

project().and("uid").previousOperation())
        GroupOperation group = Aggregation.group("uid", "date").sum("changePoints").as("totalPoints")
                .first("uid").as("uid")
                .first("date").as("date");

引用

MongoTemplate聚合(一)$lookup

Mongo学习笔记(三) 通过Aggregation和lookup进行多级关联查询

Springboot整合MongoDB系列(五)---LookupOperation关联查询

mongoTemplate关联查询

Spring Data MongoDB: Projections and Aggregations

Java使用MongoTemplate操作MangoDB,实现根据时间等条件组合查询,解决ISODate的问题


Awbeci
3.1k 声望212 粉丝

Awbeci