mongodb联表查询 -- 玛卜码

520 05-11

数据库表

表一: 数据记录表

var mongoose = require('mongoose'),
    Schema = mongoose.Schema;

var DocViewSchema = new Schema({
    realName: String, //真实姓名
    userName: String, //OA账号
    count: Number, //文档统计
    docName: String, //文档项目名称
    docType: String, //文档项目类型
    orgPath: String, //所属部门的组织路径
    orgGroup: String, //所属事业群
    orgName: String //所属小组
    time: String,
});

module.exports = mongoose.model('Doc_view', DocViewSchema);

表二：文档主表

var mongoose = require('mongoose'),
    Schema = mongoose.Schema;
var docsSchema = new Schema({
    docType:String, //文档类型
    user_name: String, //系统用户
    realName: String, //用户中文姓名
    description: String, // 文档描述
    gitUrl:String,//操作的文档源远程地址
    displayName: String, //文档展示名
    name:String,//文档名称
    logo:String,
    owner:String,//文档创建人
    developer:Array, //开发者
    realName: String,//真实姓名
    toTop:Date,//是否置顶
    dest: String, //vuepress类型文档的输出目录
    docKey: String, //约束文档为public（公开）、private（私有）类型的关键字
    privateMember: Array, //存储私有文档可查看的人员范围
    createTime: {
        type: Date,
        default: Date.now
    },
    opendFiles:Array,
    orgName: String, //所属部门
    orgPath: String, //所属部门的组织路径
    orgGroup: String, //所属事业群
    docCollection: Array,
    newGit: Boolean //标识是否是在系统上构建的新项目
});
module.exports = mongoose.model('docs', docsSchema);

表数据

表一: 展示数据

{
    "_id" : ObjectId("603678000f6ec514b4b8d02f"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "abgFeedback",
    "userName" : "zhangsan",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "张三",
    "count" : 10
}

/* 42 */
{
    "_id" : ObjectId("603678000f6ec514b4b8d030"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "bookgit",
    "userName" : "liuniu",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "刘牛",
    "count" : 1
}

/* 43 */
{
    "_id" : ObjectId("603678000f6ec514b4b8d031"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "testtestdocs",
    "userName" : "lisi",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "李四",
    "count" : 1
}

/* 44 */
{
    "_id" : ObjectId("6037c9800f6ec514b4b8d03c"),
    "__v" : 0,
    "time" : "2021-02-25",
    "docName" : "abgFeedback",
    "userName" : "zhangsan",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "张三",
    "count" : 10
}

表二：文档数据


/* 45 */
{
    "_id" : ObjectId("5f6da851a18a783210da7a16"),
    "docType" : "vuepress",
    "name" : "abgFeedback",
    "gitUrl" : "*****/abgFeedback.git",
    "description" : "问题反馈收集问题",
    "owner" : "zhangsan",
    "orgGroup" : "*****",
    "orgName" : "*****",
    "orgPath" : "****",
    "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg",
    "newGit" : false,
    "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
    "realName" : "龚成辉",
    "dest" : "abgFeedback",
    "opendFiles" : [],
    "createTime" : ISODate("2020-09-25T08:20:33.373Z"),
    "developer" : [],
    "__v" : 0,
    "docCollection" : [],
    "docKey" : "private",
    "privateMember" : ["lisi"]
}

联表查询

db.getCollection('doc_views').aggregate([
    {
        $match: {
            time: {
                $gte: "2020-03-29",
                $lte: "2021-03-29"
            }
        }
    },
    {
        $group: {
            _id: {
                "name": '$docName'
            },
            total: {
                $sum: "$count"
            }
        }
    },
    {
        $lookup: {
            from: "docs",
            localField: "_id.name",
            foreignField: "name",
            as: "child"
        }
    },
   {
       $project: {
           "child._id": false,
           "child.gitUrl": false,
           "child.description": false,
           "child.orgGroup": false,
           "child.orgPath": false,
           "child.newGit": false,
           "child.toTop": false,
           "child.docCollection": false,
           "child.opendFiles": false,
           "child.createTime": false,
           "child.logo": false,
           "child.orgName": false,
           "child.owner": false,
           "child.realName": false,
           "child.__v": false,
           "child.privateMember": false,
           "child.developer": false,
           "child.name": false
      }
    },
])

$match 聚合匹配条件这块是以doc_views表time字段匹配前端传入的起始时间和截止时间段的所有数据
$group 按某些指定的表达式对文档进行分组，然后将每个不同的分组的文档输出到下一阶段。输出文档包含一个_id字段，其中包含按关键字区分的组
1. 这里以文档名称docName为关键字进行分组统计
2. 同时给docName定义了一个name的别名并包装到_id的对象下面
3. 把所有当前时间段的同名文档进行合并并累计count字段输出total总数

$lookup 关联查询

参数	赋值	说明
from	"docs"	需要关联查询的表此处查询的事主表docs
localField	"_id.name"	匹配查询数据的表的文档名称字段传参“_id.name”的原因是：aggregate管道模式数组上一个参数是下一个参数的入参当前表文档名称关键字已经被定义到_id对象里面了
foreignField	"name"	匹配要联表的文档名称关键字即docs表文档名称关键字
as	"child"	把当前匹配到的数据输出到as定义的别名 child里面 child是一个数组

注意：以摸一个关键字如：文档名称来聚合数据是首先聚合出来的数据每个关键字只能有一条数据否则联表时无法匹配出关键字对应的主表数据

$project 可指定显示或者忽略联表里面的某些字段如上所述 false表示不显示 true表示显示

返回结果

//忽略字段的查询结果
[
  /* 22 */
  {
      "_id" : {
          "name" : "ces1234"
      },
      "total" : 5,
      "child" : [ 
          {
              "docType" : "gitbook",
              "docKey" : "private"
          }
      ]
  },

  /* 23 */
  {
      "_id" : {
          "name" : "abgFeedback"
      },
      "total" : 43,
      "child" : [ 
          {
              "docType" : "vuepress",

标签： MongoDB