MongoDB 高级查询 aggergate 聚合管道

使用聚合管道可以对集合的文档进行变换和组合。

MongoDB 中使用 db.COLLECTION_NAME.aggregate([{<stage>},...])方法来构建和使用聚合管道。

实际项目中，主要用于表关联查询、数据的统计。

管道操作符

  • $project：增加、删除、重命名字段
  • $match：条件匹配。只满足条件的文档才能进入下一阶段。
  • $limit：限制结果的数量。
  • $skip：跳过文档的数量。
  • $sort：条件排序
  • $group：条件组合结果。
  • $lookup：用于引入其它集合的数据
  • $unwind: 可以将数组中的每一个值拆分为单独的文档。

数据模拟

> db.order_item.find()
{ "_id" : ObjectId("5fc7103218fa5641d31317f9"), "order_id" : "1", "title" : "商品1", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7104318fa5641d31317fa"), "order_id" : "1", "title" : "商品键盘", "price" : "100", "num" : 1 }
{ "_id" : ObjectId("5fc7106018fa5641d31317fb"), "order_id" : "2", "title" : "牛奶", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7107718fa5641d31317fc"), "order_id" : "2", "title" : "酸奶", "price" : "50", "num" : 1 }
{ "_id" : ObjectId("5fc7108d18fa5641d31317fd"), "order_id" : "3", "title" : "零食1", "price" : "20", "num" : 1 }
{ "_id" : ObjectId("5fc710a718fa5641d31317fe"), "order_id" : "3", "title" : "方便面", "price" : "50", "num" : 5 }
> db.order.find()
{ "_id" : ObjectId("5fc71569212c818aed7b30ca"), "order_id" : "1", "trade_no" : "111", "all_price" : 100 } 
{ "_id" : ObjectId("5fc71578212c818aed7b30cb"), "order_id" : "1", "trade_no" : "112", "all_price" : 108 } 
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 } 
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 } 
{ "_id" : ObjectId("5fc715a4212c818aed7b30ce"), "order_id" : "3", "trade_no" : "332", "all_price" : 50 } 
{ "_id" : ObjectId("5fc715ac212c818aed7b30cf"), "order_id" : "3", "trade_no" : "333", "all_price" : 60 }

$project

修改文档的结构，可以用来重命名、增加或删除文档中的字段。

查找order 返回文档中order_id和all_price 字段,不显示_id 字段

db.order.aggregate([
    {
        $project:{order_id:1,all_price:1,_id:0}
    }
])

显示的字段设置为1，_id 默认显示，如果不想显示_id, 设置为0即可

$match

用于过滤文档，用法类似于find() 方法中的参数

查找order里order_id和all_price 字段并且 all_price大于等于 80 的数据

db.order.aggregate([
    {
        $project:{order_id:1,all_price:1}
    },
    {
        $match:{"all_price":{$gte:80}}
    }
])

$group

将集合中的文档进行分组，可用于统计结果。

统计每个订单的订单数量，按照订单号分组

db.order_item.aggregate([{
    {
        $group:{
            _id:"$order_id",
            total:{$sum:"$num"}
        }
    }
])
// ->
{ "_id" : "2", "total" : 2 }
{ "_id" : "3", "total" : 6 }
{ "_id" : "1", "total" : 2 }

$limit 和 $skip

$limit 和 $skip 用于限制和跳过相应的文档，与find 中 limit 与skip 类似。

取出order 里面的第三条和第四条数据

db.order.aggregate([{$skip:2},{$limit:2}])
// ->
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 }
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 }

$sort

$sort 用于对数据进行排序，1代表升序，-1代表降序。

order 里all_price 字段进行升序排列

db.order.aggregate([{$sort:{all_price:1}}])
// ->
{ "_id" : ObjectId("5fc715a4212c818aed7b30ce"), "order_id" : "3", "trade_no" : "332", "all_price" : 50 }
{ "_id" : ObjectId("5fc715ac212c818aed7b30cf"), "order_id" : "3", "trade_no" : "333", "all_price" : 60 }
{ "_id" : ObjectId("5fc71594212c818aed7b30cd"), "order_id" : "2", "trade_no" : "222", "all_price" : 80 }
{ "_id" : ObjectId("5fc71569212c818aed7b30ca"), "order_id" : "1", "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5fc71578212c818aed7b30cb"), "order_id" : "1", "trade_no" : "112", "all_price" : 108 }
{ "_id" : ObjectId("5fc7158a212c818aed7b30cc"), "order_id" : "2", "trade_no" : "221", "all_price" : 200 }

$lookup

$lookup :用于表关联查询

order 关联 order_item 查询，放入 items 中

db.order.aggregate([
    {
        $lookup:{
            from:"order_item",
            localField:"order_id",
            foreignField:"order_id",
            as:"items"
        }
    }
])

from: 数据库中指定要执行连接的集合。
localField：指定当前文档的字段。 $lookup从from集合的文档中对localField和foreignField执行相等匹配。如果当前文档不包含localField，则$lookup会将字段视为具有null的值以进行匹配。
foreignField：指定from集合中文档的字段。$lookup从from集合的文档中对localField和foreignField执行相等匹配。如果from集合中的文档不包含foreignField，则$lookup会将值视为null以便进行匹配。
as：指定生成新数组字段的名称。新的数组字段包含来自from集合的匹配文档。

// 查询出来的数据
{
    "_id": ObjectId("5fc71569212c818aed7b30ca"),
    "order_id": "1",
    "trade_no": "111",
    "all_price": 100,
    "items": [{
        "_id": ObjectId("5fc7103218fa5641d31317f9"),
        "order_id": "1",
        "title": "商品1",
        "price": "50",
        "num": 1
    }, {
        "_id": ObjectId("5fc7104318fa5641d31317fa"),
        "order_id": "1",
        "title": "商品键盘",
        "price": "100",
        "num": 1
    }]
} 
{
    "_id": ObjectId("5fc71578212c818aed7b30cb"),
    "order_id": "1",
    "trade_no": "112",
    "all_price": 108,
    "items": [{
        "_id": ObjectId("5fc7103218fa5641d31317f9"),
        "order_id": "1",
        "title": "商品1",
        "price": "50",
        "num": 1
    }, {
        "_id": ObjectId("5fc7104318fa5641d31317fa"),
        "order_id": "1",
        "title": "商品键盘",
        "price": "100",
        "num": 1
    }]
}
{
    "_id": ObjectId("5fc7158a212c818aed7b30cc"),
    "order_id": "2",
    "trade_no": "221",
    "all_price": 200,
    "items": [{
        "_id": ObjectId("5fc7106018fa5641d31317fb"),
        "order_id": "2",
        "title": "牛奶",
        "price": "50",
        "num": 1
    }, {
        "_id": ObjectId("5fc7107718fa5641d31317fc"),
        "order_id": "2",
        "title": "酸奶",
        "price": "50",
        "num": 1
    }]
}
{
    "_id": ObjectId("5fc71594212c818aed7b30cd"),
    "order_id": "2",
    "trade_no": "222",
    "all_price": 80,
    "items": [{
        "_id": ObjectId("5fc7106018fa5641d31317fb"),
        "order_id": "2",
        "title": "牛奶",
        "price": "50",
        "num": 1
    }, {
        "_id": O
        bjectId("5fc7107718fa5641d31317fc"),
        "order_id": "2",
        "title": "酸奶",
        "price": "50",
        "num": 1
    }]
}
{
    "_id": ObjectId("5fc715a4212c818aed7b30ce"),
    "order_id": "3",
    "trade_no": "332",
    "all_price": 50,
    "items": [{
        "_id": ObjectId("5fc7108d18fa5641d31317fd"),
        "order_id": "3",
        "title": "零食1",
        "price": "20",
        "num": 1
    }, {
        "_id": ObjectId("5fc710a718fa5641d31317fe"),
        "order_id": "3",
        "title": "方便面",
        "price": "50",
        "num": 5
    }]
}
{
    "_id": ObjectId("5fc715ac212c818aed7b30cf"),
    "order_id": "3",
    "trade_no": "333",
    "all_price": 60,
    "items": [{
        "_id": ObjectId("5fc7108d18fa5641d31317fd"),
        "order_id": "3",
        "title": "零食1",
        "price": "20",
        "num": 1
    }, {
        "_id": ObjectId("5fc710a718fa5641d31317fe"),
        "order_id": "3",
        "title": "方便面",
        "price": "50",
        "num": 5
    }]
}

$unwind

将数组中的每一个值拆分为单独的文档。

db.order.aggregate([
    {
        $lookup:{
            from:"order_item",
            localField:"order_id",
            foreignField:"order_id",
            as:"items"
        }
    },
    {
        $unwind:"$items"
    }
])

// 数据
{
    "_id": ObjectId("606ffe6c9a148932b00e6fc4"),
    "order_id": "1",
    "trade_no": "111",
    "all_price": 100,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fbe"),
        "order_id": "1",
        "title": "商品1",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc4"),
    "order_id": "1",
    "trade_no": "111",
    "all_price": 100,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fbf"),
        "order_id": "1",
        "title": "商品键盘",
        "price": "100",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc5"),
    "order_id": "1",
    "trade_no": "112",
    "all_price": 108,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fbe"),
        "order_id": "1",
        "title": "商品1",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc5"),
    "order_id": "1",
    "trade_no": "112",
    "all_price": 108,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fbf"),
        "order_id": "1",
        "title": "商品键盘",
        "price": "100",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc6"),
    "order_id": "2",
    "trade_no": "221",
    "all_price": 200,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc0"),
        "order_id": "2",
        "title": "牛奶",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc6"),
    "order_id": "2",
    "trade_no": "221",
    "all_price": 200,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc1"),
        "order_id": "2",
        "title": "酸奶",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc7"),
    "order_id": "2",
    "trade_no": "222",
    "all_price": 80,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc0"),
        "order_id": "2",
        "title": "牛奶",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc7"),
    "order_id": "2",
    "trade_no": "222",
    "all_price": 80,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc1"),
        "order_id": "2",
        "title": "酸奶",
        "price": "50",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc8"),
    "order_id": "3",
    "trade_no": "332",
    "all_price": 50,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc2"),
        "order_id": "3",
        "title": "零食1",
        "price": "20",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc8"),
    "order_id": "3",
    "trade_no": "332",
    "all_price": 50,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc3"),
        "order_id": "3",
        "title": "方便面",
        "price": "50",
        "num": 5
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc9"),
    "order_id": "3",
    "trade_no": "333",
    "all_price": 60,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc2"),
        "order_id": "3",
        "title": "零食1",
        "price": "20",
        "num": 1
    }
} {
    "_id": ObjectId("606ffe6c9a148932b00e6fc9"),
    "order_id": "3",
    "trade_no": "333",
    "all_price": 60,
    "items": {
        "_id": ObjectId("606ffe109a148932b00e6fc3"),
        "order_id": "3",
        "title": "方便面",
        "price": "50",
        "num": 5
    }
}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容