JAVA前線

歡迎大家關(guān)注公眾號(hào)「JAVA前線」查看更多精彩分享，主要內(nèi)容包括源碼分析、實(shí)際應(yīng)用、架構(gòu)思維、職場(chǎng)分享、產(chǎn)品思考等等，同時(shí)也非常歡迎大家加我微信「java_front」一起交流學(xué)習(xí)

1 文章概述

1.1 基本概念

MongoDB是一種非關(guān)系型數(shù)據(jù)庫(kù)，數(shù)據(jù)最終存儲(chǔ)為BSON（Binary JSON）類型。MongoDB包含三個(gè)重要邏輯概念：數(shù)據(jù)庫(kù)、集合和文檔，與關(guān)系型數(shù)據(jù)庫(kù)相關(guān)概念映射如下圖：

1.2 數(shù)據(jù)準(zhǔn)備

1.2.1 邏輯設(shè)計(jì)

bookdb數(shù)據(jù)庫(kù)包含library與book兩個(gè)集合，相關(guān)字段說(shuō)明如下圖：

1.2.2 新增數(shù)據(jù)

// 創(chuàng)建數(shù)據(jù)庫(kù)
use bookdb

// 新增圖書(shū)館
db.library.insertMany([
    {
        "_id": 1,
        "library_name": "圖書(shū)館_1"
    },
    {
        "_id": 2,
        "library_name": "圖書(shū)館_2"
    },
    {
        "_id": 3,
        "library_name": "圖書(shū)館_3"
    }
])

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

2 聚合概念

2.1 基本語(yǔ)法

MongoDB聚合語(yǔ)法第一個(gè)參數(shù)是管道：文檔在上個(gè)管道處理完后傳遞給下個(gè)管道，第二個(gè)參數(shù)是選項(xiàng)：設(shè)置聚合操作特性

db.collection.aggregate(pipeline, options)

2.2 聚合管道

常用聚合管道有以下九種類型：

$project：對(duì)文檔進(jìn)行投影

$limit：輸出管道內(nèi)前N個(gè)文檔

$skip：跳過(guò)管道內(nèi)前N個(gè)文檔

$sort：對(duì)文檔進(jìn)行排序

$out：輸出管道中文檔

$match：對(duì)文檔進(jìn)行篩選

$unwind：鋪平文檔中的數(shù)組字段

$lookup：對(duì)文檔進(jìn)行查詢

$group：對(duì)文檔進(jìn)行分組

3 聚合實(shí)例

3.1 project

1表示展示某字段

0表示不展示某字段

借閱人編號(hào)和姓名拆分成兩個(gè)數(shù)組

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

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

// 1
{
    "book_name": "圖書(shū)_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "張三",
        "李四"
    ]
}

// 2
{
    "book_name": "圖書(shū)_2",
    "borrowerIds": [
        100
    ],
    "borrowerNames": [
        "張三"
    ]
}

// 3
{
    "book_name": "圖書(shū)_3",
    "borrowerIds": [
        100,
        300
    ],
    "borrowerNames": [
        "張三",
        "王五"
    ]
}

3.2 limit

只展示一個(gè)投影結(jié)果

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

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

// 1
{
    "book_name": "圖書(shū)_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "張三",
        "李四"
    ]
}

3.3 skip

跳過(guò)一個(gè)且只展示一個(gè)投影結(jié)果

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

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

// 1
{
    "book_name": "圖書(shū)_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": "圖書(shū)_2",
    "library_id": 2
}

// 2
{
    "_id": 3,
    "book_name": "圖書(shū)_3",
    "library_id": 2
}

// 3
{
    "_id": 1,
    "book_name": "圖書(shū)_1",
    "library_id": 1
}

MongoDB內(nèi)存排序有100M限制，如果排序數(shù)據(jù)過(guò)多需要設(shè)置選項(xiàng)allowDiskUse=true，表示數(shù)據(jù)可以寫(xiě)入臨時(shí)文件進(jìn)行排序

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

3.5 out

投影結(jié)果輸出到新集合

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": "圖書(shū)_1",
    "borrowerIds": [
        100,
        200
    ],
    "borrowerNames": [
        "張三",
        "李四"
    ]
}

// 2
{
    "_id": ObjectId("62bec0636f9c37787b9590ba"),
    "book_name": "圖書(shū)_2",
    "borrowerIds": [
        100
    ],
    "borrowerNames": [
        "張三"
    ]
}

// 3
{
    "_id": ObjectId("62bec0636f9c37787b9590bb"),
    "book_name": "圖書(shū)_3",
    "borrowerIds": [
        100,
        300
    ],
    "borrowerNames": [
        "張三",
        "王五"
    ]
}

3.6 match

where book_name = xxx

db.book.aggregate([
    {
        $match: {
            "book_name": "圖書(shū)_2"
        }
    },
    {
        $project: {
            "_id": 1,
            "book_name": 1,
            "library_id": 1
        }
    }
])

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

// 1
{
    "_id": 2,
    "book_name": "圖書(shū)_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": "圖書(shū)_3",
    "book_price": 30,
    "library_id": 2
}

3.7 unwind

文檔按照借閱人數(shù)組鋪平

includeArrayIndex表示索引

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

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

// 1
{
    "_id": 1,
    "book_type": 1,
    "book_name": "圖書(shū)_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": "圖書(shū)_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": "圖書(shū)_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": "圖書(shū)_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": "圖書(shū)_3",
    "book_price": 30,
    "book_count": 100,
    "borrowers": {
        "name": "王五",
        "id": 300
    },
    "library_id": 2,
    "idx": NumberLong("1")
}

3.8 lookup

查詢圖書(shū)館有哪些圖書(shū)

lookup可以實(shí)現(xiàn)連表查詢

MongoDB 3.4之前聚合語(yǔ)法：

from：待關(guān)聯(lián)集合【book】
localField: 本集合關(guān)聯(lián)鍵【library】
foreignField：待關(guān)聯(lián)鍵【book】
as：待關(guān)聯(lián)集合數(shù)據(jù)【book】

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

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

// 1
{
    "_id": 1,
    "library_name": "圖書(shū)館_1",
    "books_info": [
        {
            "_id": 1,
            "book_type": 1,
            "book_name": "圖書(shū)_1",
            "book_price": 10,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                },
                {
                    "name": "李四",
                    "id": 200
                }
            ],
            "library_id": 1
        }
    ]
}

// 2
{
    "_id": 2,
    "library_name": "圖書(shū)館_2",
    "books_info": [
        {
            "_id": 2,
            "book_type": 2,
            "book_name": "圖書(shū)_2",
            "book_price": 20,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                }
            ],
            "library_id": 2
        },
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "圖書(shū)_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "圖書(shū)館_3",
    "books_info": [ ]
}

MongoDB 3.4之后聚合語(yǔ)法：

from：待關(guān)聯(lián)集合【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": "圖書(shū)館_1",
    "books_info": [
        {
            "_id": 1,
            "book_type": 1,
            "book_name": "圖書(shū)_1",
            "book_price": 10,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                },
                {
                    "name": "李四",
                    "id": 200
                }
            ],
            "library_id": 1
        }
    ]
}

// 2
{
    "_id": 2,
    "library_name": "圖書(shū)館_2",
    "books_info": [
        {
            "_id": 2,
            "book_type": 2,
            "book_name": "圖書(shū)_2",
            "book_price": 20,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                }
            ],
            "library_id": 2
        },
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "圖書(shū)_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "圖書(shū)館_3",
    "books_info": [ ]
}

新增價(jià)格大于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": "圖書(shū)館_1",
    "books_info": [ ]
}

// 2
{
    "_id": 2,
    "library_name": "圖書(shū)館_2",
    "books_info": [
        {
            "_id": 3,
            "book_type": 1,
            "book_name": "圖書(shū)_3",
            "book_price": 30,
            "book_count": 100,
            "borrowers": [
                {
                    "name": "張三",
                    "id": 100
                },
                {
                    "name": "王五",
                    "id": 300
                }
            ],
            "library_id": 2
        }
    ]
}

// 3
{
    "_id": 3,
    "library_name": "圖書(shū)館_3",
    "books_info": [ ]
}

3.9 group

3.9.1 簡(jiǎn)單統(tǒng)計(jì)

_id：圖書(shū)類型作為分組鍵

count：每個(gè)類型有多少種書(shū)

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

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

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

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

3.9.2 復(fù)雜統(tǒng)計(jì)

_id：圖書(shū)類型作為分組鍵

type_count：每個(gè)類型有多少種書(shū)

type_book_count：每個(gè)類型有多少本書(shū)

minTotalPrice：每個(gè)類型總價(jià)最小值

maxTotalPrice：每個(gè)類型總價(jià)最大值

totalPrice：每個(gè)類型總價(jià)

avgPrice：每個(gè)類型平均價(jià)

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：空分組鍵表示統(tǒng)計(jì)全量數(shù)據(jù)

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 文章總結(jié)

第一介紹了MongoDB與關(guān)系型數(shù)據(jù)庫(kù)關(guān)系，并且準(zhǔn)備本文需要的測(cè)試數(shù)據(jù)，第二介紹了聚合語(yǔ)法和聚合管道相關(guān)概念，第三通過(guò)實(shí)例介紹了如何使用聚合操作，希望本文對(duì)大家有所幫助。