当前位置：Gxlcms > 数据库问题 > 记一次mongodb聚合查询

记一次mongodb聚合查询

时间：2021-07-01 10:21:17 帮助过：11人阅读

　　对于一条记录有很多共有属性：主键ID、创建用户ID、创建时间、表单ID（用于读取表单配置）等一些业务数据，这里着重说一下表单ID也就是下面演示中提到的TableCode,我们这种存储方式可以将业务上的多种表都存储在现在的一张表中，所以必须要一个字段来区分这条记录存储的具体是什么信息，以方便在查询时找到具体的数据；还有一些就是具体的表单项了。我们采用的是Mongodb存储，不用想肯定是复杂结构了，数组或者键值对对象……也不知道什么原因了，我们采用的是数组，这里记作FormItems，FormItems列中存储着所有的表单项，具体的看一下数据库的存储结构：

{
    "_id" : "1",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-07",
    "FormItems" : [ 
        {
            "key" : "CarBrand",
            "value" : "红旗"
        }, 
        {
            "key" : "VehicleType",
            "value" : "H7"
        }, 
        {
            "key" : "CarNum",
            "value" : "京A00001"
        }, 
        {
            "key" : "PurchaseDate",
            "value" : "2019-01-01"
        }
    ],
    "TableCode" : "CarInfo" 
}

　　上面看到是一个缩减版的结构（为了方便演示），简单的介绍一下：TableCode-表单名称，FormItems数组中存储着所有的表单项，key本来是唯一值，这里仅仅为了演示所以如此，value就是具体的表单项的值了。查询演示用到了三张表，第一张表如上，车辆信息表：车牌子、车型、牌照、购买日期等；车辆维修表：车辆ID、花费、内容；出车记录表：车辆ID、收入、里程、备注。首先插入一些数据：

技术图片 
try{
    db.t1.insertMany([{
        "_id": "1",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-02-07",
        "FormItems": [{
            "key": "CarBrand",
            "value": "红旗"
        },
        {
            "key": "VehicleType",
            "value": "H7"
        },
        {
            "key": "CarNum",
            "value": "京A00001"
        },
        {
            "key": "PurchaseDate",
            "value": "2019-01-01"
        }],
        "TableCode": "CarInfo"
    },{
        "_id": "2",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-02-08",
        "FormItems": [{
            "key": "CarBrand",
            "value": "红旗"
        },
        {
            "key": "VehicleType",
            "value": "H5"
        },
        {
            "key": "CarNum",
            "value": "京A00002"
        },
        {
            "key": "PurchaseDate",
            "value": "2019-02-01"
        }],
        "TableCode": "CarInfo"
    },{
        "_id": "3",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-02-09",
        "FormItems": [{
            "key": "CarBrand",
            "value": "红旗"
        },
        {
            "key": "VehicleType",
            "value": "L5"
        },
        {
            "key": "CarNum",
            "value": "京A00003"
        },
        {
            "key": "PurchaseDate",
            "value": "2019-03-01"
        }],
        "TableCode": "CarInfo"
    },
    {
        "_id": "4",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-02-10",
        "FormItems": [{
            "key": "CarBrand",
            "value": "长城"
        },
        {
            "key": "VehicleType",
            "value": "哈弗H6"
        },
        {
            "key": "CarNum",
            "value": "京A00004"
        },
        {
            "key": "PurchaseDate",
            "value": "2018-03-01"
        }],
        "TableCode": "CarInfo"
    },{
        "_id": "5",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-02-09",
        "FormItems": [{
            "key": "CarBrand",
            "value": "长城"
        },
        {
            "key": "VehicleType",
            "value": "哈弗H5"
        },
        {
            "key": "CarNum",
            "value": "京A00005"
        },
        {
            "key": "PurchaseDate",
            "value": "2018-03-01"
        }],
        "TableCode": "CarInfo"
    },{
        "_id": "6",
        "CreateUserName": "ddz1",
        "CreateDate": "2018-03-09",
        "FormItems": [{
            "key": "CarBrand",
            "value": "长城"
        },
        {
            "key": "VehicleType",
            "value": "哈弗H4"
        },
        {
            "key": "CarNum",
            "value": "京A00006"
        },
        {
            "key": "PurchaseDate",
            "value": "2018-03-01"
        }],
        "TableCode": "CarInfo"
    },{
        "_id": "7",
        "CreateUserName": "ddz100",
        "CreateDate": "2018-06-06",
        "FormItems": [{
            "key": "CarId",
            "value": "1"
        },
        {
            "key": "Cost",
            "value": 100
        },
        {
            "key": "Contents",
            "value": "喷漆"
        }],
        "TableCode": "MaintenanceRecord"
    },{
        "_id": "8",
        "CreateUserName": "ddz100",
        "CreateDate": "2018-08-06",
        "FormItems": [{
            "key": "CarId",
            "value": "1"
        },
        {
            "key": "Cost",
            "value": 25
        },
        {
            "key": "Contents",
            "value": "洗车"
        }],
        "TableCode": "MaintenanceRecord"
    },{
        "_id": "9",
        "CreateUserName": "ddz101",
        "CreateDate": "2018-06-03",
        "FormItems": [{
            "key": "CarId",
            "value": "2"
        },
        {
            "key": "Cost",
            "value": 1560
        },
        {
            "key": "Contents",
            "value": "换轮胎"
        }],
        "TableCode": "MaintenanceRecord"
    },{
        "_id": "10",
        "CreateUserName": "ddz102",
        "CreateDate": "2018-06-26",
        "FormItems": [{
            "key": "CarId",
            "value": "3"
        },
        {
            "key": "Cost",
            "value": 36
        },
        {
            "key": "Contents",
            "value": "爆胎"
        }],
        "TableCode": "MaintenanceRecord"
    },{
        "_id": "11",
        "CreateUserName": "ddz103",
        "CreateDate": "2018-09-08",
        "FormItems": [{
            "key": "CarId",
            "value": "3"
        },
        {
            "key": "Cost",
            "value": 1630
        },
        {
            "key": "Contents",
            "value": "换加速器"
        }],
        "TableCode": "MaintenanceRecord"
    },{
        "_id": "12",
        "CreateUserName": "ddz10",
        "CreateDate": "2018-06-06",
        "FormItems": [{
            "key": "CarId",
            "value": "1"
        },
        {
            "key": "Income",
            "value": 106
        },
        {
            "key": "Mileage",
            "value": 50
        },
        {
            "key": "Remarks",
            "value": "123"
        }],
        "TableCode": "DispatchRecord"
    },{
        "_id": "13",
        "CreateUserName": "ddz11",
        "CreateDate": "2018-06-16",
        "FormItems": [{
            "key": "CarId",
            "value": "1"
        },
        {
            "key": "Income",
            "value": 250
        },
        {
            "key": "Mileage",
            "value": 100
        },
        {
            "key": "Remarks",
            "value": "123"
        }],
        "TableCode": "DispatchRecord"
    },{
        "_id": "14",
        "CreateUserName": "ddz12",
        "CreateDate": "2018-06-16",
        "FormItems": [{
            "key": "CarId",
            "value": "2"
        },
        {
            "key": "Income",
            "value": 1000
        },
        {
            "key": "Mileage",
            "value": 630
        },
        {
            "key": "Remarks",
            "value": "2345sfgfg"
        }],
        "TableCode": "DispatchRecord"
    },{
        "_id": "15",
        "CreateUserName": "ddz12",
        "CreateDate": "2018-08-16",
        "FormItems": [{
            "key": "CarId",
            "value": "3"
        },
        {
            "key": "Income",
            "value": 1213
        },
        {
            "key": "Mileage",
            "value": 569
        },
        {
            "key": "Remarks",
            "value": "12asdfasdfasdf3"
        }],
        "TableCode": "DispatchRecord"
    }]);
}catch(e){
    print(e);
}
View Code

　　这种存储方式有一定的好处，但是弊端也很明显：查询、排序等很费劲，如果服务器端以这样的方式返回客户端，客户端还得解析FormItems，非常不友好（感觉自己在给自己找麻烦）……所以我就想看看能不能在所有的查询操作之前，首先处理一下数据，将FormItems中的表单项都提到文档的最外层和公共字段一个层次，之后在进行查询排序似乎就简单点了，为了实现这个目标真是废了九牛二虎之力，道路相当坎坷，经历的时间也很长，不过最终好在找到一个例子解决了我们的问题：https://jira.mongodb.org/browse/SERVER-5947 。在此表示感谢，非常感谢……在这之后又遇到了一个问题就是：在关联查询时mongodb的处理方式是将所有的相关联的数据存储在一个属性中，因为我们所有的“表”都存在这一个表中，所以关联的也是这一张表，也就是关联自己查询，结果得到关联的数据还是没有处理之前的样子，相同的数据最后的结构都不一样，都不好意思返回客户端，这个问题比之前的问题更费劲……不知道度过了多少天，我的一个同事说弄出来了，我就有点……所以我也想尝试一下，最后废了半天劲儿终于弄出来，不知道和同事弄的一样不一样，这里只能吐槽mongodb你怎么能这样……看一下代码：

技术图片 
db.getCollection(‘t1‘).aggregate([
    {$addFields:
        {
            FormValueObj:{
                $arrayToObject:{
                    $map:
                     {
                       input: "$FormItems",
                       as: "field",
                       in: ["$$field.key","$$field.value"]
                     }
                }
            }
        }
    },
    {
        $addFields:{
            "FormValueObj._id":"$_id",
            "FormValueObj.CreateUserName":"$CreateUserName",
            "FormValueObj.CreateDate":"$CreateDate",
            "FormValueObj.TableCode":"$TableCode"
        }
    },
    {
        $replaceRoot: { newRoot: "$FormValueObj" }
    },
    {
        $lookup:{
           from: "t1",
           localField: "_id",
           foreignField: "FormItems.value",
           as: "RelationData1"
        }
    },
    {
        $addFields:{
            RelationData1:{
                 $map:
                 {
                    input: "$RelationData1",
                    as: "tr",
                    in: {
                        $arrayToObject:{
                            $map:
                             {
                               input:{ 
                                   $concatArrays: [ 
                                   [
                                        {key:"_id",value:"$$tr._id"},
                                        {key:"CreateUserName",value:"$$tr.CreateUserName"},
                                        {key:"CreateDate",value:"$$tr.CreateDate"},
                                        {key:"TableCode",value:"$$tr.TableCode"},
                                   ], 
                                   "$$tr.FormItems" ] 
                               },
                               as: "field",
                               in: ["$$field.key","$$field.value"]
                             }
                        }
                    }
                 }
            }
        }
    }
])
查询语句 技术图片 
/* 1 */
{
    "CarBrand" : "红旗",
    "VehicleType" : "H7",
    "CarNum" : "京A00001",
    "PurchaseDate" : "2019-01-01",
    "_id" : "1",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-07",
    "TableCode" : "CarInfo",
    "RelationData1" : [ 
        {
            "_id" : "7",
            "CreateUserName" : "ddz100",
            "CreateDate" : "2018-06-06",
            "TableCode" : "MaintenanceRecord",
            "CarId" : "1",
            "Cost" : 100.0,
            "Contents" : "喷漆"
        }, 
        {
            "_id" : "8",
            "CreateUserName" : "ddz100",
            "CreateDate" : "2018-08-06",
            "TableCode" : "MaintenanceRecord",
            "CarId" : "1",
            "Cost" : 25.0,
            "Contents" : "洗车"
        }, 
        {
            "_id" : "12",
            "CreateUserName" : "ddz10",
            "CreateDate" : "2018-06-06",
            "TableCode" : "DispatchRecord",
            "CarId" : "1",
            "Income" : 106.0,
            "Mileage" : 50.0,
            "Remarks" : "123"
        }, 
        {
            "_id" : "13",
            "CreateUserName" : "ddz11",
            "CreateDate" : "2018-06-16",
            "TableCode" : "DispatchRecord",
            "CarId" : "1",
            "Income" : 250.0,
            "Mileage" : 100.0,
            "Remarks" : "123"
        }
    ]
}

/* 2 */
{
    "CarBrand" : "红旗",
    "VehicleType" : "H5",
    "CarNum" : "京A00002",
    "PurchaseDate" : "2019-02-01",
    "_id" : "2",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-08",
    "TableCode" : "CarInfo",
    "RelationData1" : [ 
        {
            "_id" : "9",
            "CreateUserName" : "ddz101",
            "CreateDate" : "2018-06-03",
            "TableCode" : "MaintenanceRecord",
            "CarId" : "2",
            "Cost" : 1560.0,
            "Contents" : "换轮胎"
        }, 
        {
            "_id" : "14",
            "CreateUserName" : "ddz12",
            "CreateDate" : "2018-06-16",
            "TableCode" : "DispatchRecord",
            "CarId" : "2",
            "Income" : 1000.0,
            "Mileage" : 630.0,
            "Remarks" : "2345sfgfg"
        }
    ]
}

/* 3 */
{
    "CarBrand" : "红旗",
    "VehicleType" : "L5",
    "CarNum" : "京A00003",
    "PurchaseDate" : "2019-03-01",
    "_id" : "3",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-09",
    "TableCode" : "CarInfo",
    "RelationData1" : [ 
        {
            "_id" : "10",
            "CreateUserName" : "ddz102",
            "CreateDate" : "2018-06-26",
            "TableCode" : "MaintenanceRecord",
            "CarId" : "3",
            "Cost" : 36.0,
            "Contents" : "爆胎"
        }, 
        {
            "_id" : "11",
            "CreateUserName" : "ddz103",
            "CreateDate" : "2018-09-08",
            "TableCode" : "MaintenanceRecord",
            "CarId" : "3",
            "Cost" : 1630.0,
            "Contents" : "换加速器"
        }, 
        {
            "_id" : "15",
            "CreateUserName" : "ddz12",
            "CreateDate" : "2018-08-16",
            "TableCode" : "DispatchRecord",
            "CarId" : "3",
            "Income" : 1213.0,
            "Mileage" : 569.0,
            "Remarks" : "12asdfasdfasdf3"
        }
    ]
}

/* 4 */
{
    "CarBrand" : "长城",
    "VehicleType" : "哈弗H6",
    "CarNum" : "京A00004",
    "PurchaseDate" : "2018-03-01",
    "_id" : "4",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-10",
    "TableCode" : "CarInfo",
    "RelationData1" : []
}

/* 5 */
{
    "CarBrand" : "长城",
    "VehicleType" : "哈弗H5",
    "CarNum" : "京A00005",
    "PurchaseDate" : "2018-03-01",
    "_id" : "5",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-09",
    "TableCode" : "CarInfo",
    "RelationData1" : []
}

/* 6 */
{
    "CarBrand" : "长城",
    "VehicleType" : "哈弗H4",
    "CarNum" : "京A00006",
    "PurchaseDate" : "2018-03-01",
    "_id" : "6",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-03-09",
    "TableCode" : "CarInfo",
    "RelationData1" : []
}

/* 7 */
{
    "CarId" : "1",
    "Cost" : 100.0,
    "Contents" : "喷漆",
    "_id" : "7",
    "CreateUserName" : "ddz100",
    "CreateDate" : "2018-06-06",
    "TableCode" : "MaintenanceRecord",
    "RelationData1" : []
}

/* 8 */
{
    "CarId" : "1",
    "Cost" : 25.0,
    "Contents" : "洗车",
    "_id" : "8",
    "CreateUserName" : "ddz100",
    "CreateDate" : "2018-08-06",
    "TableCode" : "MaintenanceRecord",
    "RelationData1" : []
}

/* 9 */
{
    "CarId" : "2",
    "Cost" : 1560.0,
    "Contents" : "换轮胎",
    "_id" : "9",
    "CreateUserName" : "ddz101",
    "CreateDate" : "2018-06-03",
    "TableCode" : "MaintenanceRecord",
    "RelationData1" : []
}

/* 10 */
{
    "CarId" : "3",
    "Cost" : 36.0,
    "Contents" : "爆胎",
    "_id" : "10",
    "CreateUserName" : "ddz102",
    "CreateDate" : "2018-06-26",
    "TableCode" : "MaintenanceRecord",
    "RelationData1" : []
}

/* 11 */
{
    "CarId" : "3",
    "Cost" : 1630.0,
    "Contents" : "换加速器",
    "_id" : "11",
    "CreateUserName" : "ddz103",
    "CreateDate" : "2018-09-08",
    "TableCode" : "MaintenanceRecord",
    "RelationData1" : []
}

/* 12 */
{
    "CarId" : "1",
    "Income" : 106.0,
    "Mileage" : 50.0,
    "Remarks" : "123",
    "_id" : "12",
    "CreateUserName" : "ddz10",
    "CreateDate" : "2018-06-06",
    "TableCode" : "DispatchRecord",
    "RelationData1" : []
}

/* 13 */
{
    "CarId" : "1",
    "Income" : 250.0,
    "Mileage" : 100.0,
    "Remarks" : "123",
    "_id" : "13",
    "CreateUserName" : "ddz11",
    "CreateDate" : "2018-06-16",
    "TableCode" : "DispatchRecord",
    "RelationData1" : []
}

/* 14 */
{
    "CarId" : "2",
    "Income" : 1000.0,
    "Mileage" : 630.0,
    "Remarks" : "2345sfgfg",
    "_id" : "14",
    "CreateUserName" : "ddz12",
    "CreateDate" : "2018-06-16",
    "TableCode" : "DispatchRecord",
    "RelationData1" : []
}

/* 15 */
{
    "CarId" : "3",
    "Income" : 1213.0,
    "Mileage" : 569.0,
    "Remarks" : "12asdfasdfasdf3",
    "_id" : "15",
    "CreateUserName" : "ddz12",
    "CreateDate" : "2018-08-16",
    "TableCode" : "DispatchRecord",
    "RelationData1" : []
}
查询结果

　　你可能看到了查询语句太长了，这还是最基本的，幸好3.4之后提供了视图的功能，我们可以创建一个视图，弄成我们想要的样子：

技术图片 
db.createView("t1view","t1",[
    {$addFields:
        {
            FormValueObj:{
                $arrayToObject:{
                    $map:
                     {
                       input: "$FormItems",
                       as: "field",
                       in: ["$$field.key","$$field.value"]
                     }
                }
            }
        }
    },
    {
        $addFields:{
            "FormValueObj._id":"$_id",
            "FormValueObj.CreateUserName":"$CreateUserName",
            "FormValueObj.CreateDate":"$CreateDate",
            "FormValueObj.TableCode":"$TableCode"
        }
    },
    {
        $replaceRoot: { newRoot: "$FormValueObj" }
    }
])
创建视图

　　之后我们就是在视图中查询了

技术图片 
db.getCollection(‘t1view‘).aggregate([
    {$match:{_id:"1"}},
    {
        $lookup:{
           from: "t1view",
           localField: "_id",
           foreignField: "CarId",
           as: "RelationData1"
        }
    }
])
视图查询 技术图片 
/* 1 */
{
    "CarBrand" : "红旗",
    "VehicleType" : "H7",
    "CarNum" : "京A00001",
    "PurchaseDate" : "2019-01-01",
    "_id" : "1",
    "CreateUserName" : "ddz1",
    "CreateDate" : "2018-02-07",
    "TableCode" : "CarInfo",
    "RelationData1" : [ 
        {
            "CarId" : "1",
            "Cost" : 100.0,
            "Contents" : "喷漆",
            "_id" : "7",
            "CreateUserName" : "ddz100",
            "CreateDate" : "2018-06-06",
            "TableCode" : "MaintenanceRecord"
        }, 
        {
            "CarId" : "1",
            "Cost" : 25.0,
            "Contents" : "洗车",
            "_id" : "8",
            "CreateUserName" : "ddz100",
            "CreateDate" : "2018-08-06",
            "TableCode" : "MaintenanceRecord"
        }, 
        {
            "CarId" : "1",
            "Income" : 106.0,
            "Mileage" : 50.0,
            "Remarks" : "123",
            "_id" : "12",
            "CreateUserName" : "ddz10",
            "CreateDate" : "2018-06-06",
            "TableCode" : "DispatchRecord"
        }, 
        {
            "CarId" : "1",
            "Income" : 250.0,
            "Mileage" : 100.0,
            "Remarks" : "123",
            "_id" : "13",
            "CreateUserName" : "ddz11",
            "CreateDate" : "2018-06-16",
            "TableCode" : "DispatchRecord"
        }
    ]
}
视图查询结果

　　这样在操作就简单多了，还避免了上面的第二个问题,??????，不知道你有没有发现一个问题，在上面的查询结果中“RelationData1”中冗余了车辆维护表和车辆出车记录的所有相关记录，这不是我们想要的，我们想要的是RelationData1是相关的“车辆维护记录”，RelationData2是相关的“车辆出车记录”，出现这种问题的原因是关联查询时，关联的表不允许过滤，3.4版本之前就这样了，不知道大神们有没有好的办法，我只能用升级来解决了，3.6之后就可以了……

　　暂时就记到这里吧！有时间在补充。

记一次mongodb聚合查询

标签：问题   构造   agg   local   size   new   nts   form   row   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！