MongoDB related table query

foreword

I recently learned to use mongodb to query the problem of using associated tables in the middle. I encountered some problems and recorded them to share with you.

mongodb associated query

I used SQL syntax to query the relationship between oracle, sqlserver, and mysql tables before, but after I got to mongodb, I couldn't start, and the writing method was completely different, so I went to the Internet to query the writing method of mongodb related table query, so I tried to write the reference code myself. , but found that there are many problems, such as I have two tables user and apple,

user table:

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

apple table:

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

user entity class:

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

apple entity class:

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

There is a one-to-one relationship between user and apple, and now my query requirements are: query user data in descending order of the number of devices.

The query result should be:

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

We use the user table and the apple table as the main table to query, so let's try to write it using the mongodb syntax.

Use the user table as the main table query

// 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();

Note: We need to convert the id of the main table to String type before querying, because the uid of the associated table is of String type, otherwise the query fails

Use apple table as main table query

// 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();

Note: Before querying, we need to convert the uid of the main table to the ObjectId type, because the _id of the associated table is the ObjectId type, otherwise the query fails

Summarize

1. The mongodb associated query must pay attention to the conversion between the ObjectId type and the String type, otherwise it will be unsuccessful
2. The fields that appear in the project will affect the sort ordering
3. The order before and after the addition of operationList will also affect the query
4. If the query fails, I have to try it several times. I have also concluded the impact of which step occurs through continuous trial and error.
5. If possible, add the following code:

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

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

Updated 2022-01-14

Three-table association

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();

quote

MongoTemplate aggregation (1) $lookup

Mongo study notes (3) Multi-level association query through Aggregation and lookup

Springboot integrates MongoDB series (5)---

mongoTemplate associated query

Spring Data MongoDB: Projections and Aggregations

Java uses MongoTemplate to operate MangoDB, implement combined query based on time and other conditions, and solve the problem of


全栈工程师进阶
日常学习总结与分享,包括:前端、后台与运维,讲解的知识点包括:javascript、vuejs、reactjs、springb...

Awbeci

3k 声望
193 粉丝
0 条评论
推荐阅读
Helm3-安装Kakfa
前言本文介绍如何在k8s集群中使用helm来创建kafka,供大家参考学习。准备阿里云K8S集群安装helm安装Kafka我们首先添加一下helm库,并且搜索到kafka {代码...} 我们安装的Chart版本:22.1.3,App版本:3.4.0,接着...

Awbeci阅读 148

(学习到实践)七、mongodb测试,php+nginx负载均衡的部署
从测试容器中匹配搜索得到 mongod.conf.orig,设置可以启动,网上查找配置项反不能启动,原因是配置是yaml格式!好像听说过。百度查询得到:官方配置说明,网站卡得出奇。

沧浪水阅读 2.9k

MongoDB安装、启动、关闭、授权
安装参考[链接]配置文件 {代码...} 启动MongoDB {代码...} MongoDB的关闭方式kill进程模式(不建议使用) {代码...} 自带模式 {代码...} 注意: mongod进程收到SIGINT或SIGTERM信号,会做一些处理 切忌使用kill -9...

YYGP阅读 1.2k

mongoShake基于go实践应用
通过阿里云自研的MongoShake开源工具,您可以实现MongoDB数据库间的数据同步,该功能可用于数据分析、灾备和多活等业务场景。本文以云数据库MongoDB实例间的数据实时同步为例介绍配置流程。

金闽阅读 1k

如何正确地使用 Docker 在云服务器上部署 MongoDB v6 数据库并连接
使用 Docker 部署 MongoDB 时,容器默认不会自动生成 mongod.conf 配置文件,需要开发者手动映射云服务器上的配置文件至容器内部,因此,我们需要先创建一份配置文件,您可以任意选择该文件的存储目录,这里我们...

libinfs阅读 690

封面图
MongoDB的索引事物和高可用分析
collection集合,相当于SQL中的表(table),一个集合可以存放多个文档(行)。不同之处就在于集合的结构(schema)是动态的,不需要预先声明一个严格的表结构。更重要的是,默认情况下 MongoDB并不会对写入的数据做任...

djjk阅读 540

Awbeci

3k 声望
193 粉丝
宣传栏