北京
乾学院 43 号会员
2 回帖  •  4,644 浏览  • 
     ...

简化 MongoDB 关联运算

 
集算器(484) MongoDB(9) 关联计算(12) Java调用(8) 英文版(12)

1545702966257

【摘要】

        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, 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",
              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 的功能得到更充分的展现。


1545702966257

相关文章：
MongoDB 连接运算   
MongoDB 外键关联   
MongoDB 本地化排序   
MongoDB 子查询     
MongoDB 分组统计    
MongoDB 分组 topN    
MongoDB 关联运算   
MongoDB 子文档 List 字段 
协助 MongoDB 计算之交叉汇总 
将 MongoDB 导出成 csv  
协助报表开发之 MongoDB join

集算器(484) MongoDB(9) 关联计算(12) Java调用(8) 英文版(12)