简化 MongoDB 关联运算

【摘要】

        MongoDB提供的 lookup 对多表关联实现了基本的支持,但面对一些比较复杂的关联情况,往往会遇到 shell 脚本过于复杂的问题。而集算器 SPL 语言,则因其离散性、易用性恰好能弥补 Mongo 这方面的不足。若想了解更多,请前往乾学院:简化 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

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",
      "localField": "comment.comment_no",
      "foreignField": "comment_no",
      "as": "comment.comment_content"
  }},
  {  "\$unwind": "\$comment.comment_content"},
  {   "\$addFields": { "comment.comment_content":
  "\$comment.comment_content.comment_content" }},
  {   "\$group": {
      "_id": "\$_id",
      'post_no':{"\$first": "\$post_no"},
      "comment": {"\$push": "\$comment"}
      }},
  ]).pretty()

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, 247, …],[143, 3,47, …] ]
48 [[143, 148, …]]

脚本说明:
      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",
              let:   {order: "\$order", lot: "\$lot"},
              pipeline:   [{
                     \$match:   {
                     \$expr:{  \$in: [ { order: "\$\$order", lot: "$$lot"},   "$product"] }
                     }  
                     }],  
                     as:   "isCompleted"
                     }  
              },   {
                     $addFields:   {
                     "isCompleted":   {\$arrayElemAt: [ "\$isCompleted", 0] }
                     }  
              },   {
                     \$addFields:   { // add the required fields to the top level structure

                     "isCompleted":   "\$isCompleted.isCompleted",
                     "serialNo":   "\$isCompleted.serialNo",
                     "batchNo":   "\$isCompleted.batchNo"
              }  
}])

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": {
      "from": "c2",
          "localField": "user1",
          "foreignField": "user1",
          "as": "collection2_doc"
      }},

    {  "\$unwind": "\$collection2_doc"},
    {   "$redact": {
          "$cond": [
              {"\$eq": [ "\$user2",   "$collection2_doc.user2"] },
              "$$KEEP",
              "$$PRUNE"
          ]
      }},
    {   "$project": {
          "user1": 1,
          "user2": 1,
          "income": "\$income",
          "output":   "$collection2_doc. output"
      }}
      ]).pretty()

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",
    "localField" :   "workouts",
    "foreignField" :   "_id",
    "as" :   "workoutDocumentsArray"
  }},
  {$project: {   _id:0,workouts:0} } ,
  {"\$unwind":   "$workoutDocumentsArray"},
  {"\$replaceRoot": {   "newRoot":  { \$mergeObjects:   [ "$$ROOT", "$workoutDocumentsArray"] } } },
  {$project: {   workoutDocumentsArray: 0} }
  ]).pretty()

把关联表 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 的功能得到更充分的展现。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾