搜索

通过实例讲清楚MongoDB九种聚合操作

JAVA前线

共 14915字，需浏览 30分钟

 ·

2022-07-06 10:48


JAVA前线 


欢迎大家关注公众号「JAVA前线」查看更多精彩分享，主要内容包括源码分析、实际应用、架构思维、职场分享、产品思考等等，同时也非常欢迎大家加我微信「java_front」一起交流学习



1 文章概述

1.1 基本概念

MongoDB是一种非关系型数据库，数据最终存储为BSON（Binary JSON）类型。MongoDB包含三个重要逻辑概念：数据库、集合和文档，与关系型数据库相关概念映射如下图：



1.2 数据准备

1.2.1 逻辑设计

bookdb数据库包含library与book两个集合，相关字段说明如下图：



1.2.2 新增数据

// 创建数据库
use bookdb

// 新增图书馆
db.library.insertMany([
    {
        "_id": 1,
        "library_name": "图书馆_1"
    },
    {
        "_id": 2,
        "library_name": "图书馆_2"
    },
    {
        "_id": 3,
        "library_name": "图书馆_3"
    }
])

// 新增图书
db.book.insertMany([
    {
        "_id": 1,
        "book_type": 1,
        "book_name": "图书_1",
        "book_price": 10,
        "book_count": 100,
        "borrowers": [{
            "name": "张三",
            "id": 100
        }, {
            "name": "李四",
            "id": 200
        }],
        "library_id": 1
    },
    {
        "_id": 2,
        "book_type": 2,
        "book_name": "图书_2",
        "book_price": 20,
        "book_count": 100,
        "borrowers": [{
            "name": "张三",
            "id": 100
        }],
        "library_id": 2
    },
    {
        "_id": 3,
        "book_type": 1,
        "book_name": "图书_3",
        "book_price": 30,
        "book_count": 100,
        "borrowers": [{
            "name": "张三",
            "id": 100
        }, {
            "name": "王五",
            "id": 300
        }],
        "library_id": 2
    }
])

2 聚合概念

2.1 基本语法

MongoDB聚合语法第一个参数是管道：文档在上个管道处理完后传递给下个管道，第二个参数是选项：设置聚合操作特性

db.collection.aggregate(pipeline, options)


2.2 聚合管道

常用聚合管道有以下九种类型：

$project：对文档进行投影

$limit：输出管道内前N个文档

$skip：跳过管道内前N个文档

$sort：对文档进行排序

$out：输出管道中文档

$match：对文档进行筛选

$unwind：铺平文档中的数组字段

$lookup：对文档进行查询

$group：对文档进行分组


3 聚合实例

3.1 project

1表示展示某字段

0表示不展示某字段

借阅人编号和姓名拆分成两个数组

db.book.aggregate({
    $project: {
        "_id": 0,
        "book_name": 1,
        "borrowerIds": "$borrowers.id",
        "borrowerNames": "$borrowers.name"
    }
})

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

// 1
{
    "book_name": "图书_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "张三",
        "李四"
    ]
}

// 2
{
    "book_name": "图书_2",
    "borrowerIds": [
        100
    ],
    "borrowerNames": [
        "张三"
    ]
}

// 3
{
    "book_name": "图书_3",
    "borrowerIds": [
        100,
        300
    ],
    "borrowerNames": [
        "张三",
        "王五"
    ]
}

3.2 limit

只展示一个投影结果

db.book.aggregate([
    {
        $project: {
            "_id": 0,
            "book_name": 1,
            "borrowerIds": "$borrowers.id",
            "borrowerNames": "$borrowers.name"
        }
    },
    {
        $limit: 1
    }
])

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

// 1
{
    "book_name": "图书_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "张三",
        "李四"
    ]
}

3.3 skip

跳过一个且只展示一个投影结果

db.book.aggregate([
    {
        $project: {
            "_id": 0,
            "book_name": 1,
            "borrowerIds": "$borrowers.id",
            "borrowerNames": "$borrowers.name"
        }
    },
    {
        $skip: 1
    },
    {
        $limit: 1
    }
])

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

// 1
{
    "book_name": "图书_2",
    "borrowerIds": [
        100
    ],
    "borrowerNames": [
        "张三"
    ]
}

3.4 sort

db.book.aggregate([
    {
        $project: {
            "_id": 1,
            "book_name": 1,
            "library_id": 1
        }
    },
    {
        $sort: {
            "library_id": - 1,     // 降序
            "_id": 1               // 升序
        }
    }
])

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

// 1
{
    "_id": 2,
    "book_name": "图书_2",
    "library_id": 2
}

// 2
{
    "_id": 3,
    "book_name": "图书_3",
    "library_id": 2
}

// 3
{
    "_id": 1,
    "book_name": "图书_1",
    "library_id": 1
}

MongoDB内存排序有100M限制，如果排序数据过多需要设置选项allowDiskUse=true，表示数据可以写入临时文件进行排序

db.book.aggregate([
    {
        $project: {
            "_id": 1,
            "book_name": 1,
            "library_id": 1
        }
    },
    {
        $sort: {
            "library_id": - 1,
            "_id": 1
        }
    }
], {
    allowDiskUse: true
})

3.5 out

投影结果输出到新集合

db.book.aggregate([
    {
        $project: {
            "_id": 0,
            "book_name": 1,
            "borrowerIds": "$borrowers.id",
            "borrowerNames": "$borrowers.name"
        }
    },
    {
        $out: "newCollection"
    }
])

db.newCollection.find()

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

// 1
{
    "_id": ObjectId("62bec0636f9c37787b9590b9"),
    "book_name": "图书_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "张三",
        "李四"
    ]
}

// 2
{
    "_id": ObjectId("62bec0636f9c37787b9590ba"),
    "book_name": "图书_2",
    "borrowerIds": [
        100
    ],
    "borrowerNames": [
        "张三"
    ]
}

// 3
{
    "_id": ObjectId("62bec0636f9c37787b9590bb"),
    "book_name": "图书_3",
    "borrowerIds": [
        100,
        300
    ],
    "borrowerNames": [
        "张三",
        "王五"
    ]
}

3.6 match

where book_name = xxx

db.book.aggregate([
    {
        $match: {
            "book_name": "图书_2"
        }
    },
    {
        $project: {
            "_id": 1,
            "book_name": 1,
            "library_id": 1
        }
    }
])

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

// 1
{
    "_id": 2,
    "book_name": "图书_2",
    "library_id": 2
}

where library_id = 2 and price > 15

db.book.aggregate([
    {
        $match: {
            $and: [
                {
                    "library_id": 2
                },
                {
                    "book_price": {
                        $gt: 25
                    }
                }
            ]
        }
    },
    {
        $project: {
            "_id": 1,
            "book_name": 1,
            "library_id": 1,
            "book_price": 1
        }
    }
])

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

// 1
{
    "_id": 3,
    "book_name": "图书_3",
    "book_price": 30,
    "library_id": 2
}

3.7 unwind

文档按照借阅人数组铺平

includeArrayIndex表示索引

db.book.aggregate([
    {
        $unwind: {
            path: "$borrowers",
            includeArrayIndex: "idx"
        }
    }
])

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

// 1
{
    "_id": 1,
    "book_type": 1,
    "book_name": "图书_1",
    "book_price": 10,
    "book_count": 100,
    "borrowers": {
        "name": "张三",
        "id": 100
    },
    "library_id": 1,
    "idx": NumberLong("0")
}

// 2
{
    "_id": 1,
    "book_type": 1,
    "book_name": "图书_1",
    "book_price": 10,
    "book_count": 100,
    "borrowers": {
        "name": "李四",
        "id": 200
    },
    "library_id": 1,
    "idx": NumberLong("1")
}

// 3
{
    "_id": 2,
    "book_type": 2,
    "book_name": "图书_2",
    "book_price": 20,
    "book_count": 100,
    "borrowers": {
        "name": "张三",
        "id": 100
    },
    "library_id": 2,
    "idx": NumberLong("0")
}

// 4
{
    "_id": 3,
    "book_type": 1,
    "book_name": "图书_3",
    "book_price": 30,
    "book_count": 100,
    "borrowers": {
        "name": "张三",
        "id": 100
    },
    "library_id": 2,
    "idx": NumberLong("0")
}

// 5
{
    "_id": 3,
    "book_type": 1,
    "book_name": "图书_3",
    "book_price": 30,
    "book_count": 100,
    "borrowers": {
        "name": "王五",
        "id": 300
    },
    "library_id": 2,
    "idx": NumberLong("1")
}

3.8 lookup

查询图书馆有哪些图书

lookup可以实现连表查询

MongoDB 3.4之前聚合语法：

  • from：待关联集合【book】

  • localField: 本集合关联键【library】

  • foreignField：待关联键【book】

  • as：待关联集合数据【book】

db.library.aggregate([
    {
        $lookup: 
        {
            from: "book",
            localField: "_id",
            foreignField: "library_id",
            as: "books_info"
        }
    }
])

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

// 1
{
    "_id": 1,
    "library_name": "图书馆_1",
    "books_info": [
        {
            "_id": 1,
            "book_type": 1,
            "book_name": "图书_1",
            "book_price": 10,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                },
                {
                    "name": "李四",
                    "id": 200
                }
            ],
            "library_id": 1
        }
    ]
}

// 2
{
    "_id": 2,
    "library_name": "图书馆_2",
    "books_info": [
        {
            "_id": 2,
            "book_type": 2,
            "book_name": "图书_2",
            "book_price": 20,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                }
            ],
            "library_id": 2
        },
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "图书_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "图书馆_3",
    "books_info": [ ]
}

MongoDB 3.4之后聚合语法：

  • from：待关联集合【book】

  • let：声明本集合字段在管道使用

  • pipeline：操作管道

db.library.aggregate([
    {
        $lookup: 
        {
            from: "book",
            let: {
                "lid": "$_id"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                {
                                    $eq: ["$$lid", "$library_id"]
                                }
                            ]
                        }
                    }
                }
            ],
            as: "books_info"
        }
    }
])

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

// 1
{
    "_id": 1,
    "library_name": "图书馆_1",
    "books_info": [
        {
            "_id": 1,
            "book_type": 1,
            "book_name": "图书_1",
            "book_price": 10,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                },
                {
                    "name": "李四",
                    "id": 200
                }
            ],
            "library_id": 1
        }
    ]
}

// 2
{
    "_id": 2,
    "library_name": "图书馆_2",
    "books_info": [
        {
            "_id": 2,
            "book_type": 2,
            "book_name": "图书_2",
            "book_price": 20,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                }
            ],
            "library_id": 2
        },
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "图书_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "图书馆_3",
    "books_info": [ ]
}

新增价格大于20查询条件

db.library.aggregate([
    {
        $lookup: 
        {
            from: "book",
            let: {
                "lid": "$_id"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                {
                                    $eq: ["$$lid", "$library_id"]
                                },
                                {
                                    $gt: ["$book_price", 20]
                                }
                            ]
                        }
                    }
                }
            ],
            as: "books_info"
        }
    }
])

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

// 1
{
    "_id": 1,
    "library_name": "图书馆_1",
    "books_info": [ ]
}

// 2
{
    "_id": 2,
    "library_name": "图书馆_2",
    "books_info": [
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "图书_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "张三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "图书馆_3",
    "books_info": [ ]
}

3.9 group

3.9.1 简单统计

_id：图书类型作为分组键

count：每个类型有多少种书

db.book.aggregate([
    {
        $group: {
            _id: "$book_type",
            count: {
                $sum: 1
            }
        }
    }
])

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

// 1
{
    "_id": 2,
    "count": 1
}

// 2
{
    "_id": 1,
    "count": 2
}

3.9.2 复杂统计

_id：图书类型作为分组键

type_count：每个类型有多少种书

type_book_count：每个类型有多少本书

minTotalPrice：每个类型总价最小值

maxTotalPrice：每个类型总价最大值

totalPrice：每个类型总价

avgPrice：每个类型平均价

db.book.aggregate([
    {
        $group: {
            _id: "$book_type",
            type_count: {
                $sum: 1
            },
            type_book_count: {
                $sum: "$book_count"
            },
            minTotalPrice: {
                $min: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            maxTotalPrice: {
                $max: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            totalPrice: {
                $sum: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            avgPrice: {
                $avg: "$book_price"
            }
        }
    }
])

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

// 1
{
    "_id": 2,
    "type_count": 1,
    "type_book_count": 100,
    "minTotalPrice": 2000,
    "maxTotalPrice": 2000,
    "totalPrice": 2000,
    "avgPrice": 20
}

// 2
{
    "_id": 1,
    "type_count": 2,
    "type_book_count": 200,
    "minTotalPrice": 1000,
    "maxTotalPrice": 3000,
    "totalPrice": 4000,
    "avgPrice": 20
}

3.9.3 空分组键

_id：空分组键表示统计全量数据

db.book.aggregate([
    {
        $group: {
            _id: null,
            type_count: {
                $sum: 1
            },
            type_book_count: {
                $sum: "$book_count"
            },
            minTotalPrice: {
                $min: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            maxTotalPrice: {
                $max: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            totalPrice: {
                $sum: {
                    $multiply: ["$book_price", "$book_count"]
                }
            },
            avgPrice: {
                $avg: "$book_price"
            }
        }
    }
])

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

// 1
{
    "_id": null,
    "type_count": 3,
    "type_book_count": 300,
    "minTotalPrice": 1000,
    "maxTotalPrice": 3000,
    "totalPrice": 6000,
    "avgPrice": 20
}

4 文章总结

第一介绍了MongoDB与关系型数据库关系，并且准备本文需要的测试数据，第二介绍了聚合语法和聚合管道相关概念，第三通过实例介绍了如何使用聚合操作，希望本文对大家有所帮助。



JAVA前线 


欢迎大家关注公众号「JAVA前线」查看更多精彩分享，主要内容包括源码分析、实际应用、架构思维、职场分享、产品思考等等，同时也非常欢迎大家加我微信「java_front」一起交流学习


浏览 24
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
Arthas之实例操作
1. 静态类属性操作获取public静态属性ognl -c 7cd84586 '@com.system.framework.ArtahsDemoClassLoader@pubTestPrex'ognl -c 7cd84586 "@com.system.framework.ArtahsDemoClassLoader@pubfinalTestPrex"输出各式 @Ty...
_herbert
0
Python操作MongoDB数据库(二)
咪哥杂谈
0
Python操作MongoDB数据库（一）
咪哥杂谈
0
嵌入式Linux操作UART实例
嵌入式Linux
0
TmongoDBPHP的mongodb操作类TmongoDB
TmongoDB 是一个用PHP开发的快速易于使用的 mongodb操作类=============
TmongoDBPHP的mongodb操作类TmongoDB
0
Python也能操作MongoDB数据库
Python爬虫与数据挖掘
0
TmongoDBPHP的mongodb操作类TmongoDB
TmongoDB是一个用PHP开发的快速易于使用的mongodb操作类======================================TmongoDB快速轻量级.TmongoDB易于使用,
TmongoDBPHP的mongodb操作类TmongoDB
0
通过实例理解Go逃逸分析
Go语言精选
0
通过实例理解 Go 逃逸分析
CoderPark
0
九种奥义书
九种奥义书
0
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报