demo_10_06 云数据库聚合_lookup_02

// 1. 数据库数据
// {
//  "authors": { // 集合(表名)
//      "data": [ // 数据
//          {"_id": 1, "name": "author 1", "intro": "Two-time best-selling sci-fiction novelist"},
//          {"_id": 3, "name": "author 3", "intro": "UCB assistant professor"},
//          {"_id": 4, "name": "author 4", "intro": "major in CS"}
//      ]
//  },
//  "books": { // 集合(表名)
//      "data": [ // 数据
//          {"_id":"book1","authors":["author 1"],"category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
//          {"_id":"book3","authors":["author 3", "author 4"],"category":"science","stock":30,"title":"science 1"},
//          {"_id":"book4","authors":["author 3"],"category":"science","stock":40,"title":"science 2"}
//      ]
//  }
// }

// 02. 聚合操作 lookup 对数组字段应用相等匹配
// 聚合阶段,联表查询。与同个数据库下的一个指定的集合做 left outer join(左外连接)。对该阶段的每一个输入记录,
// lookup 会在该记录中增加一个数组字段,该数组是被联表中满足匹配条件的记录列表。lookup 会将连接后的结果输出给下个阶段。

// 左连接:以左边表为基表,左表有的右表也有就也出来,右表没有的就NULL,右连接和左连接相反
// 内连接:两个表的交集,就是左表和右表都有的才显示出来
// 全连接:连个表的并集(UNION:并集不重复,UNION ALL:并集重复)
// 交叉连接:有两种,显式的和隐式的,不带ON子句,返回的是两表的乘积,也叫笛卡尔积.
// 左外连接:以左表为主表,右表没数据为null
// 右外连接:以右表为主表,左表没数据为null
// 全外连接:全外 = 左外 UNION 右外
‘use strict‘;
const db = uniCloud.database();
const $ = db.command.aggregate;
exports.main = async(event, context) => {
    let res = await db.collection(‘authors‘).aggregate()
        // where authors.name in books.authors
        .lookup({
            from: ‘books‘,
            localField: ‘name‘,
            foreignField: ‘authors‘,
            as: ‘publishedBooks‘,
        })
        .end();
    return res;
};

// 聚合之后的返回值
// {
//  "affectedDocs": 3,
//  "data": [{
//          "_id": 1,
//          "intro": "Two-time best-selling sci-fiction novelist",
//          "name": "author 1",
//          // 左外连接,插入符合条件的查询结果集
//          "publishedBooks": [{
//              "_id": "book1",
//              "authors": ["author 1"],
//              "category": "novel",
//              "stock": 10,
//              "time": 1564456048486,
//              "title": "novel 1"
//          }]
//      },
//      {
//          "_id": 3,
//          "intro": "UCB assistant professor",
//          "name": "author 3",
//          // 左外连接,插入符合条件的查询结果集
//          "publishedBooks": [{
//                  "_id": "book3",
//                  "authors": ["author 3", "author 4"],
//                  "category": "science",
//                  "stock": 30,
//                  "title": "science 1"
//              },
//              {
//                  "_id": "book4",
//                  "authors": ["author 3"],
//                  "category": "science",
//                  "stock": 40,
//                  "title": "science 2"
//              }
//          ]
//      },
//      {
//          "_id": 4,
//          "intro": "major in CS",
//          "name": "author 4",
//          // 左外连接,插入符合条件的查询结果集
//          "publishedBooks": [{
//              "_id": "book3",
//              "authors": ["author 3", "author 4"],
//              "category": "science",
//              "stock": 30,
//              "title": "science 1"
//          }]
//      }
//  ]
// }

demo_10_06 云数据库聚合_lookup_02

上一篇:WIN7安装COGNOS8后配置IIS网站后,访问COGNOS站点网页一直显示空白,解决方法(转载)


下一篇:C#利用QrCode.Net生成二维码(Qr码