记一次Mongodb数据库查询之包含所有指定元素的数组或者都在指定元素的数组中du

database

  这里记录一个查询需求:数据库中字段的值(数组类型)都在指定的数组中。举例说一下实际场景,数据库中一个字段存储用户“可以使用的编程语言”,一般都会是多个,所以该字段是数组格式。现在要查询的是:会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: "随便写点什么"

}

]

}),

]);

View Code

  看一下插入的数据:

   这里数据结构和之前表单生成器(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 ] }

}

}

])

  来一张截图,看一下查询结果:

以上是 记一次Mongodb数据库查询之包含所有指定元素的数组或者都在指定元素的数组中du 的全部内容, 来源链接: utcz.com/z/536190.html

回到顶部