MongoDB aggregation (aggregate) common operations and examples

Introduction

Aggregate in MongoDB is mainly used to process data (such as statistical average, sum, etc.) and return the calculated data result.

Somewhat similar to SQL statement COUNT (*) .

Common operations

expression	description
$match	Used to filter data, and only output documents that meet the conditions. $match uses MongoDB's standard query operations.
$project	Modify the structure of the input document. Can be used to rename, add or delete fields, and can also be used to create calculation results and nested documents.
$limit	Used to limit the number of documents returned by the MongoDB aggregation pipeline.
$skip	Skip the specified number of documents in the aggregation pipeline and return the remaining documents.
$unwind	Split a certain array type field in the document into multiple pieces, each containing one value in the array.
$group	Group the documents in the collection, which can be used for statistical results.
$sort	Sort the input documents and output them.
$lookup	Union table query
$geoNear	Output ordered documents close to a certain geographic location.
$facet/$bucket	Category search (supported above MongoDB 3.4)

 

Example

$match

//查询条件 以某个分组查询 （可以多个查询条件累加到match里面）
db.getCollection('doc_views').aggregate([
    {
        $match: { "orgName" : "前端技术部" }
    }
])
//查询结果如下
/* 1 */
{
    "_id" : ObjectId("5fcb2c6cdfebd2682b2ae01b"),
    "__v" : 0,
    "time" : "2020-11-11",
    "docName" : "testPublishAll",
    "orgGroup" : "汽车事业群",
    "orgName" : "前端技术部",
    "count" : 3
}
...

$project

//删减字段
db.getCollection('doc_views').aggregate([
    {
        $match: { "orgName" : "前端技术部" }
    },
    {
        $project: {
            docName: true,
            orgName: true
        }
    }
])
//查询结果如下 只显示设置为true的字段
/* 1 */
{
    "_id" : ObjectId("5fcb2c6cdfebd2682b2ae01b"),
    "docName" : "testPublishAll",
    "orgName" : "前端技术部"
}
...
//定义别名 把orgGroup指定指定成count字段
db.getCollection('doc_views').aggregate([
    {
        $match: { "orgName" : "前端技术部" }
    },
    {
        $project: {
            docName: true,
            orgName: true,
            orgGroup: "$count"
        }
    }
])
//查询结果orgGroup字段展示count的值
/* 1 */
{
    "_id" : ObjectId("5fcb2c6cdfebd2682b2ae01b"),
    "docName" : "testPublishAll",
    "orgName" : "前端技术部",
    "orgGroup" : 3
}

$group

//聚合条件 _id ：强制必须存在。 
db.getCollection('doc_views').aggregate([
    {
        $group: { 
            _id: { name: "$docName", orgName: "$orgName" },
           total: { $sum: "$count" } 
        }
    }
    
])
//1、_id: null  _id为null的查询结果
/* 1 */
{
    "_id" : null,
    "total" : 474
}
//2、_id: "$docName" _id为某字段
/* 1 */
{
    "_id" : "gch-gitbook",
    "total" : 8
}
...
//3、_id: { name: "$docName", orgName: "$orgName" } _id查询条件并为字段定义别名
/* 1 */
{
    "_id" : {
        "name" : "vuepress-hjw-test",
        "orgName" : "前端技术部"
    },
    "total" : 2
}

$sort、$skip、$limit

//列表分页的时候一般三个联合使用来做数据分页
//sort列表排序 skip指定跳过多少条 limit每次查询条数
//skip的计算方式：(当前页码-1)*每页大小 如：(pageIndex - 1) * pageSize
db.getCollection('doc_views').aggregate([
    {$sort: {_id: -1}},
    {$skip:Number(100)},
    {$limit:Number(10)} 
])
//查询结果如下
/* 1 */
{
    "_id" : ObjectId("604ecc57b3b65a411b5600de"),
    "__v" : 0,
    "time" : "2021-02-25",
    "docName" : "abgFeedback",
    "docType" : "vuepress",
    "orgGroup" : "汽车事业群",
    "orgName" : "前端技术部",
    "count" : 1
}

/* 2 */
{
    "_id" : ObjectId("604ecc57b3b65a411b5600dd"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "testtestdocs",
    "docType" : "docsify",
    "orgGroup" : "汽车事业群",
    "orgName" : "前端技术部",
    "count" : 1
}

$lookup

//联表查询
db.getCollection('doc_views').aggregate([
    {$sort: {_id: -1}},
    {$skip:Number(100)},
    {$limit:Number(10)},
    {$lookup:{
        from: "docs",
        localField: "docName",
        foreignField: "name",
        as: "child"
    }}
])
//查询结果
/* 1 */
{
    "_id" : ObjectId("604ecc57b3b65a411b5600de"),
    "__v" : 0,
    "time" : "2021-02-25",
    "docName" : "abgFeedback",
    "docType" : "vuepress",
    "orgGroup" : "汽车事业群",
    "orgName" : "前端技术部",
    "count" : 1,
    "child" : [ 
        {
            "_id" : ObjectId("5f6da851a18a783210da7a16"),
            "docType" : "vuepress",
            "name" : "abgFeedback",
            "description" : "二手车问题反馈收集问题",
            "owner" : "gongchenghui",
            "orgGroup" : "汽车事业群",
            "orgName" : "前端技术部",
            "newGit" : false,
            "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
            "realName" : "龚成辉",
            "dest" : "abgFeedback",
            "opendFiles" : [],
            "createTime" : ISODate("2020-09-25T08:20:33.373Z")
        }
    ]
}

$facet category query

//根据条件 分类一次查询出想要的数据和数据的总数
db.getCollection('doc_views').aggregate([
    {$match: {"orgName" : "前端技术部"}},
    {
        $facet: {
            respData: [
                {$sort: {_id: -1}},
                {$skip:Number(100)},
                {$limit:Number(1)},
            ],
            total: [
                {
                    $count: "total"
                }
            ]
        }
    }
//查询结果如下
  /* 1 */
{
    "respData" : [ 
        {
            "_id" : ObjectId("604ecc57b3b65a411b5600de"),
            "__v" : 0,
            "time" : "2021-02-25",
            "docName" : "abgFeedback",
            "docType" : "vuepress",
            "orgGroup" : "汽车事业群",
            "orgName" : "前端技术部",
            "count" : 1
        }
    ],
    "total" : [ 
        {
            "total" : 284
        }
    ]
}

Note: This method can only be used in databases of version 3.4 or higher. For lower versions, you still need to do two queries