MongoDB 的高级查询 aggregate 聚合管道

一、MongoDB 聚合管道（Aggregation Pipeline）

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

实际项目：表关联查询、数据的统计。

MongoDB 中使用 db.COLLECTION_NAME.aggregate([{<stage>},...]) 方法 来构建和使用聚合管道。
先看下官网给的实例，感受一下聚合管道的用法。

二、MongoDB Aggregation 管道操作符与表达式

SQL 和 NOSQL 对比:

管道表达式: 管道操作符作为“键”,所对应的“值”叫做管道表达式。

例如{KaTeX parse error: Expected 'EOF', got '}' at position 19: …ch:{status:"A"}}̲，match 称为管道操作符，而 status:"A"称为管道表达式， 是管道操作符的操作数(Operand)。

每个管道表达式是一个文档结构，它是由字段名、字段值、和一些表达式操作符组成的。

三、 数据模拟

db.order.insert({"order_id":"1","uid":10,"trade_no":"111","all_price":100,"all_num":2}) 
db.order.insert({"order_id":"2","uid":7,"trade_no":"222","all_price":90,"all_num":2}) 
db.order.insert({"order_id":"3","uid":9,"trade_no":"333","all_price":20,"all_num":6}) 


db.order_item.insert({"order_id":"1","title":"商品鼠标 1","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品键盘 2","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品键盘 3","price":0,num:1}) 
db.order_item.insert({"order_id":"2","title":"牛奶","price":50,num:1})
db.order_item.insert({"order_id":"2","title":"酸奶","price":40,num:1}) 
db.order_item.insert({"order_id":"3","title":"矿泉水","price":2,num:5})
db.order_item.insert({"order_id":"3","title":"毛巾","price":10,num:1})

四、 $project

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

要求查找 order 只返回文档中order_id, trade_no 和 all_price 字段

五、 $match 作用用于过滤文档。

用法类似于 find() 方法中的参数。

六、 $group

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

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

七、 $sort

将集合中的文档进行排序。

八、 $limit

九、 $skip

十、 $lookup 表关联

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

查询结果：

// 1
{
    "_id": ObjectId("606ecfbdbb390000fa004964"),
    "order_id": "1",
    "uid": 10,
    "trade_no": "111",
    "all_price": 100,
    "all_num": 2,
    "items": [
        {
            "_id": ObjectId("606ecfbdbb390000fa004967"),
            "order_id": "1",
            "title": "商品鼠标 1",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa004968"),
            "order_id": "1",
            "title": "商品键盘 2",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa004969"),
            "order_id": "1",
            "title": "商品键盘 3",
            "price": 0,
            "num": 1
        }
    ]
}

// 2
{
    "_id": ObjectId("606ecfbdbb390000fa004965"),
    "order_id": "2",
    "uid": 7,
    "trade_no": "222",
    "all_price": 90,
    "all_num": 2,
    "items": [
        {
            "_id": ObjectId("606ecfbdbb390000fa00496a"),
            "order_id": "2",
            "title": "牛奶",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa00496b"),
            "order_id": "2",
            "title": "酸奶",
            "price": 40,
            "num": 1
        }
    ]
}

// 3
{
    "_id": ObjectId("606ecfbdbb390000fa004966"),
    "order_id": "3",
    "uid": 9,
    "trade_no": "333",
    "all_price": 20,
    "all_num": 6,
    "items": [
        {
            "_id": ObjectId("606ecfbdbb390000fa00496c"),
            "order_id": "3",
            "title": "矿泉水",
            "price": 2,
            "num": 5
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa00496d"),
            "order_id": "3",
            "title": "毛巾",
            "price": 10,
            "num": 1
        }
    ]
}

db.order.aggregate([{
    $lookup: {
        from: "order_item",
        localField: "order_id",
        foreignField: "order_id",
        as: "items"
    }
}, {
    $project: {
        trade_no: 1,
        all_price: 1,
        items: 1
    }
}, {
    $match: {
        "all_price": {
            $gte: 90
        }
    }
}, {
    $sort: {
        "all_price":  - 1
    }
}, ])

查询结果：

// 1
{
    "_id": ObjectId("606ecfbdbb390000fa004964"),
    "trade_no": "111",
    "all_price": 100,
    "items": [
        {
            "_id": ObjectId("606ecfbdbb390000fa004967"),
            "order_id": "1",
            "title": "商品鼠标 1",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa004968"),
            "order_id": "1",
            "title": "商品键盘 2",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa004969"),
            "order_id": "1",
            "title": "商品键盘 3",
            "price": 0,
            "num": 1
        }
    ]
}

// 2
{
    "_id": ObjectId("606ecfbdbb390000fa004965"),
    "trade_no": "222",
    "all_price": 90,
    "items": [
        {
            "_id": ObjectId("606ecfbdbb390000fa00496a"),
            "order_id": "2",
            "title": "牛奶",
            "price": 50,
            "num": 1
        },
        {
            "_id": ObjectId("606ecfbdbb390000fa00496b"),
            "order_id": "2",
            "title": "酸奶",
            "price": 40,
            "num": 1
        }
    ]
}