聚合查询 aggregate

aggregate

语法

db.collection.aggregate(pipeline, options)
// pipeline Array
// option Document 可选

pipeline有很多stage，简单记录常用的：

• $count
• $group
• $match 查询条件
• $project
• $unwind
• $limit
• $skip
• $sort
• $sortByCount
• $lookup
• $out
• $addFields
• $in

$count

// 数据
{ "_id" : 1, "subject" : "History", "score" : 88 }
{ "_id" : 2, "subject" : "History", "score" : 92 }
{ "_id" : 3, "subject" : "History", "score" : 97 }
{ "_id" : 4, "subject" : "History", "score" : 71 }
{ "_id" : 5, "subject" : "History", "score" : 79 }
{ "_id" : 6, "subject" : "History", "score" : 83 }

// 查询
db.getCollection('test').aggregate(
    [ 
        { $match: { score: {$gt: 80} },
        { $count: 'passing_score' }
    ]
)

// 输出
{
    passing_score: 4
}

$match

查询条件

$unwind

将数组数据拆分为单条，例如

// 数据
{ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] }

// 查询
db.getCollection('test').aggregate(
    [
        { $unwind : "$sizes" }
    ]
)

// 输出
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

$group

指定输出格式，按照_id为唯一标识分组，例如

// 数据
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-03-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-03-01T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-03-15T09:00:00Z") }
{ "_id" : 4, "item" : "xyz", "price" : 5, "quantity" : 20, "date" : ISODate("2014-04-04T11:21:39.736Z") }
{ "_id" : 5, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-04-04T21:23:13.331Z") }

// 查询
// 按月份，日期和年份对文档进行分组，并计算total price和average quantity，并计算每个组的文档数量：
db.collection.aggregate([   
    {
        $group: { 
            _id: {
                month: { $month: '$date' },
                day: { $dayOfMonth: '$date' },
                year: { $year: '$date' }
            }, 
            totalPrice: { $sum: { $multiply: ['$quantity', '$price'] } },
            averageQuantity: { $avg: '$quantity'}
            myCount: { $sum: 1 } 
        }
    }
])

// 输出
/* 1 */
{
    "_id" : {
        "month" : 4,
        "day" : 4,
        "year" : 2014
    },
    "totalPrice" : 200,
    "averageQuantity" : 15.0,
    "count" : 2.0
}

/* 2 */
{
    "_id" : {
        "month" : 3,
        "day" : 15,
        "year" : 2014
    },
    "totalPrice" : 50,
    "averageQuantity" : 10.0,
    "count" : 1.0
}

/* 3 */
{
    "_id" : {
        "month" : 3,
        "day" : 1,
        "year" : 2014
    },
    "totalPrice" : 40,
    "averageQuantity" : 1.5,
    "count" : 2.0
}

如果_id为null，则输出则不分组，统计全部数据

db.collection.aggregate([   
    {
        $group: { 
            _id: null, 
            totalPrice: { $sum: { $multiply: ['$quantity', '$price'] } },
            averageQuantity: { $avg: '$quantity'}
            myCount: { $sum: 1 } 
        }
    }
])

// 输出
{
    "_id": null,
    "totalPrice": 290,
    "averageQuantity": 8.6,
    "count": 5.0
}

$project

从文档中选择想要的字段，和不想要的字段，示例

// 数据
{
  "_id" : 1,
  title: "abc123",
  isbn: "0001122223334",
  author: { last: "zzz", first: "aaa" },
  copies: 5,
  lastModified: "2016-07-28"
}

// 查询
db.getCollection('test').aggregate( [ { $project : { title : 1 , author : 1 } } ] )

// 输出
{ "_id" : 1, "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" } }

// 查询
db.test.aggregate( [ { $project : { "author.first" : 0, "lastModified" : 0 } } ] )

// 输出
{
   "_id" : 1,
   "title" : "abc123",
   "isbn" : "0001122223334",
   "author" : {
      "last" : "zzz"
   },
   "copies" : 5,
}

$group + $project

db.collection.aggregate( [
    // 这里myCount自定义，相当于mysql的
    // select count(*) as myCount

    //  id为null代表不分组， 一个数据
    { $group: { _id: null, myCount: { $sum: 1 } } }, 
    { $project: { _id: 0 } }  // 返回不显示_id字段
] )


// 输出
{
    myCount：2
}

$skip

跳过n条数据

$limit

返回前n条数据

$sort

排序，1升序，-1降序

$lookup

关联查询

db.collection.aggregate( [
    $lookup: {
        from: 'class',
        localField: 'class.classId',
        foreignField: 'id',
        as: 'class'
      }
    }
] )

$ddFields

db.collection.aggregate( [
    { $ddFields: { _id: '$id' } }  // 返回数据会新增一个属性_id，值为数据表中id的值
] )