Mongoose聚合管道aggregate多表关联查询（$project、$sort、$match、$limit 、$skip、$group 、$lookup）

末蓝、 2021-07-25 21:50 1518阅读 0赞

**目录**

一、$project 修改文档的结构

二、$sort 排序

三、$match,筛选

四、$limit 限制

五、$skip 跳过

六、$group 分组

七、$lookup 多表联查

--------------------

结构目录：

![20210303140128972.png][]

db.js

const mongoose = require('mongoose')
     
    mongoose.connect("mongodb://127.0.0.1:27017/testdb", {useNewUrlParser:true},function (err) {
        if (err) {
            console.log(err)
            return
        }
        console.log("数据库连接成功！")
    })
    
    module.exports = mongoose;

order.js

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70][]

var mongoose = require('./db.js');
    var OrderSchema = mongoose.Schema({
        order_id: {
            type: String,
        },
        uid: Number,
        trade_no: String,
        all_price: Number,
        all_num: Number,
    })
    
    module.exports = mongoose.model('Order', OrderSchema, 'order');

order\_item.js

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]

var mongoose = require('./db.js');
    var OrderItemSchema = mongoose.Schema({
        order_id: {
            type: String,
        },
        title: String,
        price: Number,
        num: Number,
    })
    
    module.exports = mongoose.model('OrderItem', OrderItemSchema, 'order_item');

向order表中插入数据

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2][]

向order\_item表中插入数据

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

## 一、$project 修改文档的结构 ##

可以用来重命名，增加或者删除文档中的字段  
要求查找 order 只返回文档中 trade\_no 和 all\_price 字段。

$project中的字段值 为1表示筛选该字段，为0表示过滤该字段

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3][]

## 二、$sort 排序 ##

1为升序，-1为降序

**升序：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4][]

**降序：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5][]

## 三、$match,筛选 ##

得到总价格>=90的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6][]

## 四、$limit 限制 ##

比如只查一条数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 7][]

## 五、$skip 跳过 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 8][]

## 六、$group 分组 ##

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 9][]

![20210303144809247.png][]

## 七、$lookup 多表联查 ##

1、在order中通过order\_id获取order\_item

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 10][]

打印出的数据如下所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 11][]

打印出的数据有点多，还可以使用$project获取指定的字段，如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 12][]

打印结果数据如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 13][]

2、根据order\_item,查询order的信息

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 14][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 15][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 16][]

[20210303140128972.png]: /images/20210725/64b8a273c4c1497cb41b50063c83e5da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70]: /images/20210725/9e880a1ff643460f8a347236d62518eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1]: /images/20210725/cdc934aab88c41309636562a533b5631.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2]: /images/20210725/1aa58195ff4f4da6b94d561a24af81df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3]: /images/20210725/5ce34545cba84d53a20cf4a47672681b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4]: /images/20210725/01c3916023124bb29153f1ba25d0d4a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5]: /images/20210725/7703906c273747cb910dc450f73cf5ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6]: /images/20210725/8228334c8bb249c7a2c09f191c1b0522.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 7]: /images/20210725/3a422c73d77b4400a8b93d4ac94226d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 8]: /images/20210725/3dd21e18817347adb21b3181043f7787.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 9]: /images/20210725/f7332605adf143a9a5543bcc37e7a1ea.png
[20210303144809247.png]: /images/20210725/a2304758d0924b82a90a73bd225edb90.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 10]: /images/20210725/2452d517dded4dafa51aff0fad9c6b63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 11]: /images/20210725/f684c3bbf6374370a5f5d3e23c249519.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 12]: /images/20210725/8e6af27c5a564e958e5214343c1b69c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 13]: /images/20210725/17a7209c108a41d7baf440a1b703ead4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 14]: /images/20210725/75fa31f7910a4a918d4fafe9b8766ec8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20210303151717354.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 16]: /images/20210725/0c3685df666e4b848e702822ea1b754a.png