Mongodb join table query

Time:2021-4-19

Database table

Table 1: data record table

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

var DocViewSchema = new Schema({
    RealName: string, // real name
    User name: string, // OA account
    Count: number, // document statistics
    Docname: string, // document project name
    DOCTYPE: string, // document item type
    Orgpath: string, // the organization path of the Department
    Orggroup: string, // business group
    Orgname: string // group
    time: String,
});

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

Table 2: document main table

var mongoose = require('mongoose'),
    Schema = mongoose.Schema;
var docsSchema = new Schema({
    docType:String , // document type
    user_ Name: string, // system user
    RealName: string, // user's Chinese name
    Description: string, // document description
    gitUrl:String , // the remote address of the document source for the operation
    DisplayName: string, // document display name
    name:String , // document name
    logo:String,
    owner:String , // document creator
    developer:Array , // Developer
    RealName: string, // real name
    toTop:Date , // top
    Dest: string, // output directory of vuepress type documents
    Dockey: string, // constrains the document to be a public or private keyword
    Privatemember: array, // stores the personnel range that private documents can view
    createTime: {
        type: Date,
        default: Date.now
    },
    opendFiles:Array,
    Orgname: string, // Department
    Orgpath: string, // the organization path of the Department
    Orggroup: string, // business group
    docCollection: Array,
    Newgit: Boolean // identifies whether a new project is built on the system
});
module.exports = mongoose.model('docs', docsSchema);

Table data

Table 1: display data

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

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

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

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

Table 2: document data

/* 45 */
{
    "_id" : ObjectId("5f6da851a18a783210da7a16"),
    "docType" : "vuepress",
    "name" : "abgFeedback",
    "gitUrl" : "*****/abgFeedback.git",
    "Description": "problem feedback collection problem,",
    "owner" : "zhangsan",
    "orgGroup" : "*****",
    "orgName" : "*****",
    "orgPath" : "****",
    "logo" : "https://imgs.developpaper.com/imgs/58logo.jpg",
    "newGit" : false,
    "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
    "RealName": "Gong Chenghui,",
    "dest" : "abgFeedback",
    "opendFiles" : [],
    "createTime" : ISODate("2020-09-25T08:20:33.373Z"),
    "developer" : [],
    "__v" : 0,
    "docCollection" : [],
    "docKey" : "private",
    "privateMember" : ["lisi"]
}

Joint table query

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
      }
    },
])
  1. The $match aggregate matching condition is based on Doc? U views The table time field matches all the data of the start time and end time period passed in by the front end
  2. $group groups documents according to certain specified expressions, and then outputs each different grouped document to the next stage. The output document contains a_idField that contains groups distinguished by keyword

    1. Here, the document name docname is used as the keyword for grouping statistics
    2. At the same time, a name alias is defined for docname and packaged into_ Under the object of ID
    3. Merge all documents with the same name in the current period and output the total in the count field
  3. $lookup associated query

    parameter assignment explain
    from “docs” Table to be associated with query
    localField “_id.name” The document name field of the table that matches the query data is transferred to the parameter “query”_ id.name ”The reason is: one parameter on the aggregate pipeline pattern array is the input parameter of the next parameter. The current table document name keyword has been defined to_ It’s in the ID object
    foreignField “name” Match the document name keyword to be associated with the table, that is, the document name keyword of the doc table
    as “child” Output the currently matched data to the alias child defined by as, where child is an array

    Note: to aggregate data with a keyword, such as document name, is the first aggregated data. Each keyword can only have one piece of data. Otherwise, the main table data corresponding to the keyword cannot be matched when joining tables

  4. $project can specify to display or ignore some fields in the join table. As mentioned above, false means not to display and true means to display

Return results

//Ignore query results for fields
[
  /* 22 */
  {
      "_id" : {
          "name" : "ces1234"
      },
      "total" : 5,
      "child" : [ 
          {
              "docType" : "gitbook",
              "docKey" : "private"
          }
      ]
  },

  /* 23 */
  {
      "_id" : {
          "name" : "abgFeedback"
      },
      "total" : 43,
      "child" : [ 
          {
              "docType" : "vuepress",
              "dest" : "abgFeedback",
              "docKey" : "private"
          }
      ]
  }
]
//Do not ignore the return result of the field
[
  /* 22 */
  {
      "_id" : {
          "name" : "ces1234"
      },
      "total" : 5,
      "child" : [ 
          {
              "_id" : ObjectId("5da19272f76a861008c468ff"),
              "docType" : "gitbook",
              "name" : "ces1234",
              "gitUrl" : "***/docs/ces1234.git",
              "Description": "app front end group business document,",
              "owner" : "zhangsan",
              "opendFiles" : [],
              "createTime" : ISODate("2019-10-12T08:44:34.986Z"),
              "developer" : [ "lisi" ],
              "__v" : 0,
              "orgPath" : "****",
              "orgName" : "****",
              "orgGroup" : "****",
              "toTop" : ISODate("2020-06-12T02:09:20.255Z"),
              "logo" : "https://imgs.developpaper.com/imgs/58logo.jpg",
              "docCollection" : [],
              "docKey" : "private",
              "privateMember" : [],
              "RealName": "Gong Chenghui,",
              "DisplayName": "test project"
          }
      ]
  },

  /* 23 */
  {
      "_id" : {
          "name" : "abgFeedback"
      },
      "total" : 43,
      "child" : [ 
          {
              "_id" : ObjectId("5f6da851a18a783210da7a16"),
              "docType" : "vuepress",
              "name" : "abgFeedback",
              "gitUrl" : "****/docs/abgFeedback.git",
              "Description": "problem feedback collection problem,",
              "owner" : "gongchenghui",
              "orgGroup" : "****",
              "orgName" : "****",
              "orgPath" : "****",
              "logo" : "https://imgs.developpaper.com/imgs/58logo.jpg",
              "newGit" : false,
              "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
              "RealName": "Gong Chenghui,",
              "dest" : "abgFeedback",
              "opendFiles" : [],
              "createTime" : ISODate("2020-09-25T08:20:33.373Z"),
              "developer" : [],
              "__v" : 0,
              "docCollection" : [],
              "docKey" : "private",
              "privateMember" : [ 
                  "wangwu"
              ],
              "DisplayName": "front end problem feedback"
          }
      ]
  }
]