记一次Mongodb数据库查询之包含所有指定元素的数组或者都在指定元素的数组中
这里记录一个查询需求：数据库中字段的值（数组类型）都在指定的数组中。举例说一下实际场景，数据库中一个字段存储用户“可以使用的编程语言”，一般都会是多个，所以该字段是数组格式。现在要查询的是：会c#、javascript或者只会c#或者只会javascript的用户，翻译一下就是数据库中字段的值是子集而给定的数组是全集。这种查询需要在mongodb没有找到特定的查询操作符，这篇笔记主要解决这个问题，顺便介绍一下"$all"运算符。"$all"查询的是数据库字段的值包含所有指定元素的数组，也就是数据库中字段的值是全集而给定的数组是子集，和前面提到的需求相反。
为了演示上述的两种查询需求，先造一些测试数据，下面是表结构：
|
编程语言调查表（FormId: 507048044944694000, FormVersion: 507048044944694001） |
||||
|
唯一标识 |
中文描述 |
控件类型 |
是否必填 |
表单项的其他配置（在表单设计时配置，文本框长度、时间格式等） |
|
1572493554001 |
用户 |
选择人员控件 |
是 |
|
|
1572493554002 |
可以使用的编程语言 |
复选框 |
否 |
|
|
1572493554003 |
最喜欢的编程语言 |
文本框 |
否 |
|
|
1572493554004 |
工作地点 |
文本框 |
否 |
|
|
1572493554005 |
工作年限 |
数值输入框 |
否 |
|
|
1572493554006 |
备注 |
多行文本框 |
否 |
|
下面是造数据的语句
var GV_TableName = "FormInstace", GV_FormId = "507048044944694000", GV_FormVersion = "507048044944694001", GV_CreateUserIds = ["user10000", "user10001", "user10002", "user10003", "user10004", "user10005", "user10006", "user10007", "user10008", "user10009"]; var GV_LangObj = { 1: { id: "1", text: "C#" }, 2: { id: "2", text: "JavaScript" }, 3: { id: "3", text: "HTML" }, 4: { id: "4", text: "CSS" }, 5: { id: "5", text: "Go" }, 6: { id: "6", text: "Rust" } }; var GV_Name2Id = { "userName": "1572493554001", "lang": "1572493554002", "favLang": "1572493554003", "workPlace": "1572493554004", "workYears": "1572493554005", "remarks": "1572493554006", }; var getGUID = function () { return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function (c) { var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8); return v.toString(16).toUpperCase(); }); } var getFormInstanceOtherAttrs = function (formId, formVersion) { var tempCreateUserIdIndex = Math.floor(Math.random() * GV_CreateUserIds.length), tempCreateDate = ISODate(); return { _id: getGUID(), ExtendData: {}, CreateUserId: GV_CreateUserIds[tempCreateUserIdIndex], CreateUserName: GV_CreateUserIds[tempCreateUserIdIndex], CreateDate: tempCreateDate, LastModifyDate: tempCreateDate, FormId: GV_FormId, FormVersion: GV_FormVersion }; }; var assembleFormInstance = function (formItemsAttr) { return Object.assign(formItemsAttr, getFormInstanceOtherAttrs()); } //************************************************************************************************************************************************ // 批量插入数据 db[GV_TableName].insertMany([ assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u1" }, { key: GV_Name2Id.lang, value: [GV_LangObj['1'], GV_LangObj['2'], GV_LangObj['3'], GV_LangObj['4']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['1']] }, { key: GV_Name2Id.workPlace, value: "北京" }, { key: GV_Name2Id.workYears, value: 1 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u2" }, { key: GV_Name2Id.lang, value: [GV_LangObj['1'], GV_LangObj['2'], GV_LangObj['6']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['6']] }, { key: GV_Name2Id.workPlace, value: "天津" }, { key: GV_Name2Id.workYears, value: 2 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u3" }, { key: GV_Name2Id.lang, value: [GV_LangObj['1'], GV_LangObj['2']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['1']] }, { key: GV_Name2Id.workPlace, value: "石家庄" }, { key: GV_Name2Id.workYears, value: 3 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u4" }, { key: GV_Name2Id.lang, value: [GV_LangObj['1'], GV_LangObj['5']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['5']] }, { key: GV_Name2Id.workPlace, value: "上海" }, { key: GV_Name2Id.workYears, value: 4 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u5" }, { key: GV_Name2Id.lang, value: [GV_LangObj['1']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['1']] }, { key: GV_Name2Id.workPlace, value: "广州" }, { key: GV_Name2Id.workYears, value: 5 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u6" }, { key: GV_Name2Id.lang, value: [GV_LangObj['2']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['2']] }, { key: GV_Name2Id.workPlace, value: "深圳" }, { key: GV_Name2Id.workYears, value: 6 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u7" }, { key: GV_Name2Id.lang, value: [] }, { key: GV_Name2Id.favLang, value: [] }, { key: GV_Name2Id.workPlace, value: "成都" }, { key: GV_Name2Id.workYears, value: 7 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), assembleFormInstance({ FormItems: [{ key: GV_Name2Id.userName, value: "u8" }, { key: GV_Name2Id.lang, value: [GV_LangObj['5'], GV_LangObj['6']] }, { key: GV_Name2Id.favLang, value: [GV_LangObj['5']] }, { key: GV_Name2Id.workPlace, value: "重庆" }, { key: GV_Name2Id.workYears, value: 8 }, { key: GV_Name2Id.remarks, value: "随便写点什么" } ] }), ]);
看一下插入的数据：
这里数据结构和之前表单生成器(Form Builder)之表单数据存储结构mongodb篇文章中介绍的一样。为了方便查看，将“可以使用的编程语言”字段从“FormItems”数组中拿出来并放在最外层，下面是语句
// 通用聚合管道（将“编程语言”表单项从"FormItems"中拷贝一份放到最外层，方便查看） var showLangItemPrePipeline = [{ $addFields: { FormItemObj: { $arrayToObject: { $map: { input: "$FormItems", as: "field", in: [ "$$field.key", "$$field.value" ] } } } } }, { $addFields: { "LangFormItem": "$FormItemObj.1572493554002", } }, { $addFields: { "1572493554002": { $reduce: { input: "$LangFormItem", initialValue: "", in: { $concat: ["$$value", "$$this.text", ","] } } }, } }, { $project: { 'FormItemObj': 0, 'LangFormItem': 0 } } ]; // 1、查询：展示一下插入的示例数据 db.getCollection("FormInstace").aggregate(showLangItemPrePipeline);
下面看一下查询效果：
说明：从制造假数据的语句中你可以看到“1572493554002”字段是数组类型并且每一项都是一个对象，上图中将数组拼接成了字符串，方便查看。
先来看一下数据库中字段的值都在指定元素的数组中的查询语句：
// 通用聚合管道（将“编程语言”表单项从"FormItems"中拷贝一份放到最外层，方便查看） var showLangItemPrePipeline = [{ $addFields: { FormItemObj: { $arrayToObject: { $map: { input: "$FormItems", as: "field", in: [ "$$field.key", "$$field.value" ] } } } } }, { $addFields: { "LangFormItem": "$FormItemObj.1572493554002", } }, { $addFields: { "1572493554002": { $reduce: { input: "$LangFormItem", initialValue: "", in: { $concat: ["$$value", "$$this.text", ","] } } }, } }, { $project: { 'FormItemObj': 0, 'LangFormItem': 0 } } ]; db.getCollection("FormInstace").aggregate(showLangItemPrePipeline.concat([{ "$match": { "FormId": "507048044944694000", "FormItems": { "$elemMatch": { "key": "1572493554002", "value.0": { '$exists': true }, "value": { "$not": { "$elemMatch": { "text": { "$nin": ["C#", "JavaScript"] } } } } } } } } ]))
来一张截图，看一下查询结果：
注意：这里语句中还用到了“$exists”运算符，如果不添加这个会将数组长度为0的查出来。参考链接。
在看一下“$all”查询，数据库字段的值包含所有指定元素的数组：
// 通用聚合管道（将“编程语言”表单项从"FormItems"中拷贝一份放到最外层，方便查看） var showLangItemPrePipeline = [{ $addFields: { FormItemObj: { $arrayToObject: { $map: { input: "$FormItems", as: "field", in: [ "$$field.key", "$$field.value" ] } } } } }, { $addFields: { "LangFormItem": "$FormItemObj.1572493554002", } }, { $addFields: { "1572493554002": { $reduce: { input: "$LangFormItem", initialValue: "", in: { $concat: ["$$value", "$$this.text", ","] } } }, } }, { $project: { 'FormItemObj': 0, 'LangFormItem': 0 } } ]; db.getCollection('FormInstace').aggregate(showLangItemPrePipeline.concat([{ "$match": { "FormId": "507048044944694000", "FormItems": { "$elemMatch": { "key": "1572493554002", "value.text": { "$all": ["C#", "JavaScript"] } } } } } ]))
来一张截图，看一下查询结果：
这里在顺便记录一下在mongodb中数值转字符串，高版本有“$toString”操作符（版本4.0）、“$convert”操作符（版本4.0）……但是低版本的该如何处理，参考链接。这个例子比较简单，就不写制造数据的语句了，直接来查询语句：
db.getCollection('test001').aggregate([
{
$addFields: {
"ageStr": { $substr: [ "$num", 0, -1 ] }
}
}
])
来一张截图，看一下查询结果：
