简化MongoDB关联运算
MongoDB属于 NOSQL 中的基于分布式文件存储的文档型数据库,这种bson格式的文档结构,更加贴近我们对物体各方面的属性描述。而在使用 MongoDB 存储数据的过程中,有时候难免需要进行关联表查询。自从 MongoDB 3.2 版本后,它提供了 $lookup 进行关联表查询,让查询功能改进了不少。但在实现应用场景中,所遇到的环境错综复杂,问题解决也非易事,脚本书写起来也并不简单。好在有了集算器 SPL 语言的协助,处理起来就相对容易多了。
本文我们将针对 MongoDB 在关联运算方面的问题进行讨论分析,并通过集算器 SPL 语言加以改进,方便用户使用 MongoDB。讨论将分为以下几个部分:
1. 关联嵌套结构情况 1…………………………………………….. 1
2. 关联嵌套结构情况 2…………………………………………….. 3
3. 关联嵌套结构情况 3…………………………………………….. 4
4. 两表关联查询………………………………………………………. 6
5. 多表关联查询………………………………………………………. 8
6. 关联表中的数组查找…………………………………………… 10
Java 应用程序调用 DFX 脚本…………………………………… 12
创新互联建站2013年开创至今,先为通辽等服务建站,通辽等地企业,进行企业商务咨询服务。为通辽企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
1.关联嵌套结构情况1
两个关联表,表 A 与表 B 中的内嵌文档信息关联, 且返回的信息在内嵌文档中。表 childsgroup 字段 childs 是嵌套数组结构,需要合并的信息 name 在其下。
测试数据:
history:
_id | id | History | child_id |
1 | 001 | today worked | ch001 |
2 | 002 | Working | ch004 |
3 | 003 | now working | ch009 |
childsgroup:
_id | gid | name | childs |
1 | g001 | group1 | {"id":"ch001","info":{"name":"a",mobile:1111}},{"id":"ch002","info":{"name":"b",mobile:2222}} |
2 | g002 | group1 | {"id":"ch004","info":{"name":"c",mobile:3333}},{"id":"ch009","info":{"name":"d",mobile:4444}} |
表History中的child_id与表childsgroup中的childs.id关联,希望得到下面结果:
{
"_id" : ObjectId("5bab2ae8ab2f1bdb4f434bc3"),
"id" : "001",
"history" : "today worked",
"child_id" : "ch001",
"childInfo" :
{
"name" : "a",
" mobile" : 1111
}
………………
}
Mongo 脚本
db.history.aggregate([ {$lookup: { from: "childsgroup", let: {child_id: "$child_id"}, pipeline: [ {$match: { $expr: { $in: [ "$$child_id", "$childs.id"] } } }, {$unwind: "$childs"}, {$match: { $expr: { $eq: [ "$childs.id", "$$child_id"] } } }, {$replaceRoot: { newRoot: "$childs.info"} } ], as: "childInfo" }}, {"$unwind": "$childInfo"} ]) |
这个脚本用了几个函数lookup、pipeline、match、unwind、replaceRoot处理,一般 mongodb 用户不容易写出这样复杂脚本;那么我们再看看 spl 脚本是如何实现的:
SPL脚本 ( 文件名:childsgroup.dfx)
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"history.find()").fetch() | |
3 | =mongo_shell(A1,"childsgroup.find()").fetch() | |
4 | =A3.conj(childs) | |
5 | =A2.join(child_id,A4:id,info) | |
6 | >A1.close() |
关联查询结果:
_id | id | history | child_id | info |
1 | 001 | today worked | ch001 | [a,1111] |
2 | 002 | working | ch004 | [c,3333] |
3 | 003 | now working | ch009 | [d,4444] |
脚本说明:
A1:连接 mongodb 数据库。
A2:获取 history 表中的数据。
A3:获取 childsgroup 表中的数据。
A4:将 childsgroup 中的 childs 数据提取出来合并成序表。
A5:表 history 中的 child_id 与表 childs 中的 id 关联查询,追加 info 字段, 返回序表。
A6:关闭数据库连接。
相对 mongodb 脚本写法,SPL 脚本的难度降低了不少,思路也更加清晰,也不需要再去熟悉有关 mongo 函数的用法,以及如何去组合处理数据等,节约了不少时间。
2.关联嵌套结构情况 2
两个关联表,表 A 与表 B 中的内嵌文档信息关联, 将信息合并到内嵌文档中。表 txtPost 字段 comment 是嵌套数组结构,需要把 comment_content 合并到其下。
txtComment:
_ID | comment_no | comment_content |
1 | 143 | test test |
2 | 140 | math |
txtPost
_ID | post_no | Comment |
1 | 48 | [{"comment_no" : 143, "comment_group" : 1} ] |
2 | 47 | [{"comment_no" : 140, "comment_group" : 2}, {"comment_no" : 143, "comment_group" : 3} ] |
期望结果:
_ID | post_no | Comment |
1 | 48 | [{"comment_no" : 143, "comment_group" : 1,"comment_content" : "test test"} ] |
2 | 47 | [{"comment_no" : 140, "comment_group" : 2,"comment_content" : "math"}, {"comment_no" : 143, "comment_group" : 3,"comment_content" : "test test"} ] |
Mongo 脚本
db.getCollection("txtPost").aggregate([ { "$unwind": "$comment"}, { "$lookup": { "from": "txtComment", |
表txtPost 按 comment 拆解成记录,然后与表 txtComment 关联查询,将其结果放到数组中,再将数组拆解成记录,将comment_content 值移到 comment 下,最后分组合并。
SPL 脚本:
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"txtPost.find()").fetch() | |
3 | =mongo_shell(A1,"txtComment.find()").fetch() | |
4 | =A2.conj(comment.derive(A2.post_no:pno)) | |
5 | =A4.join(comment_no,A3:comment_no,comment_content:Content) | |
6 | =A5.group(pno;~:comment) | |
7 | >A1.close() |
关联查询结果:
pno | Comment |
47 | [[ 140, 2,47, …],[143, 3,47, …] ] |
48 | [[143, 1,48, …]] |
脚本说明:
A1:连接 mongodb 数据库。
A2:获取 txtPost 表中的数据。
A3:获取 txtComment 表中的数据。
A4:将序表 A2 下的 comment 与 post_no 组合成序表,其中 post_no 改名为 pno。
A5:序表 A4 通过 comment_no 与序表 A3 关联,追加字段 comment_content,将其改名为 Content。
A6:按 pno 分组返回序表,~ 表示当前记录。
A7:关闭数据库连接。
Mongo、SPL 脚本实现方式类似,都是把嵌套结构的数据转换成行列结构的数据,再分组合并。但 SPL 脚本的实现更简单明了。
3.关联嵌套结构情况 3
两个关联表,表 A 与表 B 中的内嵌文档信息关联, 且返回的信息在记录上。表 collection2 字段 product 是嵌套数组结构,返回的信息是 isCompleted 等字段 。
测试数据:
collection1:
{
_id: '5bc2e44a106342152cd83e97',
description
{
status: 'Good',
machine: 'X'
},
order: 'A',
lot: '1'
};
collection2:
{
_id: '5bc2e44a106342152cd83e80',
isCompleted: false,
serialNo: '1',
batchNo: '2',
product: [ // note the subdocuments here
{order: 'A', lot: '1'},
{order: 'A', lot: '2'}
]
}
期待结果
{
_id: 5bc2e44a106342152cd83e97,
description:
{
status: 'Good',
machine: 'X',
},
order: 'A',
lot: '1' ,
isCompleted: false,
serialNo: '1',
batchNo: '2'
}
Mongo 脚本
db.collection1.aggregate([{ $lookup: { from: "collection2", |
lookup 两表关联查询,首个 addFields获取isCompleted数组的第一个记录,后一个addFields 转换成所需要的几个字段信息
SPL脚本:
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"collection1.find()").fetch() | |
3 | =mongo_shell(A1,"collection2.find()").fetch() | |
4 | =A3.conj(A2.select(order:A3.product.order,lot:A3.product.lot).derive(A3.serialNo:sno,A3.batchNo:bno)) | |
5 | >A1.close() |
脚本说明:
A1:连接 mongodb 数据库。
A2:获取 collection1 表中的数据。
A3:获取 collection2 表中的数据。
A4:根据条件 order, lot 从序表 A2 中查询记录,然后追加序表 A3 中的字段 serialNo, batchNo,返回合并后的序表。
A5:关闭数据库连接。
Mongo、SPL 脚本都实现了预期的结果。SPL 很清晰地实现了从数据记录中的内嵌结构中筛选,将符合条件的数据合并成新序表。
4.两表关联查询
从关联表中选择所需要的字段组合成新表。
Collection1:
user1 | user2 | income |
1 | 2 | 0.56 |
1 | 3 | 0.26 |
collection2:
user1 | user2 | output |
1 | 2 | 0.3 |
1 | 3 | 0.4 |
2 | 3 | 0.5 |
期望结果:
user1 | user2 | income | output |
1 | 2 | 0.56 | 0.3 |
1 | 3 | 0.26 | 0.4 |
Mongo 脚本
db.c1.aggregate([ |
lookup 两表进行关联查询,redact 对记录根据条件进行遍历处理,project 选择要显示的字段。
SPL脚本:
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"c1.find()").fetch() | |
3 | =mongo_shell(A1,"c2.find()").fetch() | |
4 | =A2.join(user1:user2,A3:user1:user2,output) | |
5 | >A1.close() |
脚本说明:
A1:连接 mongodb 数据库。
A2:获取c1表中的数据。
A3:获取c2表中的数据。
A4:两表按字段 user1,user2 关联,追加序表 A3 中的 output 字段,返回序表。
A5:关闭数据库连接。
Mongo、SPL 脚本都实现了预期的结果。SPL 通过 join 把两个关联表不同的字段合并成新表,与关系数据库用法类似。
5.多表关联查询
多于两个表的关联查询,结合成一张大表。
Doc1:
_id | firstName | lastName |
U001 | shubham | verma |
Doc2:
_id | userId | address | mob |
2 | U001 | Gurgaon | 9876543200 |
Doc3:
_id | userId | fbURLs | twitterURLs |
3 | U001 | http://www.facebook.com | http://www.twitter.com |
合并后的结果:
{
"_id" : ObjectId("5901a4c63541b7d5d3293766"),
"firstName" : "shubham",
"lastName" : "verma",
"address" : {
"address" : "Gurgaon"
},
"social" : {
"fbURLs" : "http://www.facebook.com",
"twitterURLs" : "http://www.twitter.com"
}
}
Mongo 脚本
db.doc1.aggregate([ {$match: { _id: ObjectId("5901a4c63541b7d5d3293766") } }, { $lookup: { from: "doc2", localField: "_id", foreignField: "userId", as: "address" } }, { $unwind: "$address" }, { $project: { "address._id": 0, "address.userId": 0, "address.mob": 0 } }, { $lookup: { from: "doc3", localField: "_id", foreignField: "userId", as: "social" } }, { $unwind: "$social" }, { $project: { "social._id": 0, "social.userId": 0 } } ]).pretty(); |
由于 Mongodb 数据结构原因,写法也多样化,展示也各不相同。
SPL脚本:
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"doc1.find()").fetch() | |
3 | =mongo_shell(A1,"doc2.find()").fetch() | |
4 | =mongo_shell(A1,"doc3.find()").fetch() | |
5 | =A2.join(_id,A3:userId,address,mob) | |
6 | =A5.join(_id,A4:userId,fbURLs,twitterURLs) | |
7 | >A1.close() |
Mongo、SPL 脚本都实现了预期的结果。此 SPL 脚本与上面例子类似,只是多了一个关联表,每次 join 就新增加字段,最后叠加构成一张大表。
SPL 脚本的简洁性、统一性非常明显。
6.关联表中的数组查找
从关联表记录数据组中查找符合条件的记录, 用给定的字段组合成新表。
测试数据:
users:
_id | Name | workouts |
1000 | xxx | [2,4,6] |
1002 | yyy | [1,3,5] |
workouts:
_id | Date | Book |
1 | 1/1/2001 | Othello |
2 | 2/2/2001 | A Midsummer Night's Dream |
3 | 3/3/2001 | The Old Man and the Sea |
4 | 4/4/2001 | GULLIVER’S TRAVELS |
5 | 5/5/2001 | Pickwick Papers |
6 | 6/6/2001 | The Red and the Black |
期望结果:
Name | _id | Date | Book |
xxx | 2 | 2/2/2001 | A Midsummer Night's Dream |
xxx | 4 | 4/4/2001 | GULLIVER’S TRAVELS |
xxx | 6 | 6/6/2001 | The Red and the Black |
yyy | 1 | 1/1/2001 | Othello |
yyy | 3 | 3/3/2001 | The Old Man and the Sea |
yyy | 5 | 5/5/2001 | Pickwick Papers |
Mongo 脚本
db.users.aggregate([ { "$lookup": { "from" : "workouts", |
把关联表 users,workouts 查询结果放到数组中,再将数组拆解,提升子记录的位置,去掉不需要的字段。
SPL脚本 (users.dfx):
A | B | |
1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") | |
2 | =mongo_shell(A1,"users.find()").fetch() | |
3 | =mongo_shell(A1,"workouts.find()").fetch() | |
4 | =A2.conj(A3.select(A2.workouts^~.array(_id)!=[]).derive(A2.name)) | |
5 | >A1.close() |
脚本说明:
A1:连接 mongodb 数据库。
A2:获取users表中的数据。
A3:获取workouts表中的数据。
A4:查询序表 A3 的 _id 值存在于序表A2中 workouts 数组的记录, 并追加 name 字段。返回合并的序表。
A5:关闭数据库连接。
由于需要获取序列的交集不为空为条件,故将 _id 转换成序列。
Mongo、SPL 脚本都实现了预期的结果。从脚本实现过程来看,SPL 集成度高而又不失灵活性,让程序简化了不少。
7.Java 应用程序调用 DFX 脚本
在通过 SPL 脚本对 MongoDB 数据进行了关联计算后,其结果可以被 java 应用程序很容易地使用。集算器提供了 JDBC 驱动程序,用 JDBC 存储过程方式访问,与调用存储过程相同。(JDBC 具体配置参考《集算器教程》中的“ JDBC 基本使用”章节 )
Java 调用主要过程如下:
public void testUsers(){
Connection con = null;
com.esproc.jdbc.InternalCStatement st;
try{
// 建立连接
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
// 调用存储过程,其中 users 是 dfx 的文件名
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call users> ()");
// 执行存储过程
st.execute();
// 获取结果集
ResultSet rs = st.getResultSet();
。。。。。。。
catch(Exception e){
System.out.println(e);
}
可以看到,使用时按标准的 JDBC 方法操作,集算器很方便嵌入到 Java 应用程序中。同时,集算器也支持 ODBC 驱动,因此集成到其它支持 ODBC 的语言也非常容易。
Mongo 存储的数据结构相对关系数据库更复杂、更灵活,其提供的查询语言也非常强、适应面广,同时需要了解函数也不少,函数之间的结合更是变化无穷,因此要熟练掌握并应用也并非易事。集算器的离散性、易用性恰好能弥补 Mongo 这方面的不足,在降低 mongo 学习成本及使用复杂度、难度的同时,让 mongo 的功能得到更充分的展现。
名称栏目:简化MongoDB关联运算
文章出自:http://scyanting.com/article/jjidjj.html