记一次mongodb聚合查询
先说一下场景,产品中用到了简单的表单构造器,开始提供了一系列的控件,例如单行文本框、多行文本框、单选、复选、时间等,之后你可以拖拽控件自己组装你想要的表单……网上有很多的表单构造器,这里就不细说了,可能功能有多有少,但是原理类似。因为这种表单的这种实现方式,我们不单单要存储表单的真实数据,还要存储对应表单的配置数据来渲染表单的样子。这里不细说配置相关的事情,细说一下表单数据的存储和查询。
对于一条记录有很多共有属性:主键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、收入、里程、备注。首先插入一些数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); }
这种存储方式有一定的好处,但是弊端也很明显:查询、排序等很费劲,如果服务器端以这样的方式返回客户端,客户端还得解析FormItems,非常不友好(感觉自己在给自己找麻烦)……所以我就想看看能不能在所有的查询操作之前,首先处理一下数据,将FormItems中的表单项都提到文档的最外层和公共字段一个层次,之后在进行查询排序似乎就简单点了,为了实现这个目标真是废了九牛二虎之力,道路相当坎坷,经历的时间也很长,不过最终好在找到一个例子解决了我们的问题:https://jira.mongodb.org/browse/SERVER-5947 。在此表示感谢,非常感谢……在这之后又遇到了一个问题就是:在关联查询时mongodb的处理方式是将所有的相关联的数据存储在一个属性中,因为我们所有的“表”都存在这一个表中,所以关联的也是这一张表,也就是关联自己查询,结果得到关联的数据还是没有处理之前的样子,相同的数据最后的结构都不一样,都不好意思返回客户端,这个问题比之前的问题更费劲……不知道度过了多少天,我的一个同事说弄出来了,我就有点……所以我也想尝试一下,最后废了半天劲儿终于弄出来,不知道和同事弄的一样不一样,这里只能吐槽mongodb你怎么能这样……看一下代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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"] } } } } } } } ])
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* 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之后提供了视图的功能,我们可以创建一个视图,弄成我们想要的样子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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" } } ])
之后我们就是在视图中查询了
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
db.getCollection('t1view').aggregate([ {$match:{_id:"1"}}, { $lookup:{ from: "t1view", localField: "_id", foreignField: "CarId", as: "RelationData1" } } ])
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* 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之后就可以了……
暂时就记到这里吧!有时间在补充。