这里记录一个查询需求：数据库中字段的值（数组类型）都在指定的数组中。举例说一下实际场景，数据库中一个字段存储用户“可以使用的编程语言”，一般都会是多个，所以该字段是数组格式。现在要查询的是：会c#、javascript或者只会c#或者只会javascript的用户，翻译一下就是数据库中字段的值是子集而给定的数组是全集。这种查询需要在mongodb没有找到特定的查询操作符，这篇笔记主要解决这个问题，顺便介绍一下"$all"运算符。"$all"查询的是数据库字段的值包含所有指定元素的数组，也就是数据库中字段的值是全集而给定的数组是子集，和前面提到的需求相反。

　　为了演示上述的两种查询需求，先造一些测试数据，下面是表结构：

编程语言调查表（FormId: 507048044944694000, FormVersion: 507048044944694001）

唯一标识

中文描述

控件类型

是否必填

表单项的其他配置（在表单设计时配置，文本框长度、时间格式等）

1572493554001

用户

选择人员控件

是

1572493554002

可以使用的编程语言

复选框

否

1572493554003

最喜欢的编程语言

文本框

否

1572493554004

工作地点

文本框

否

1572493554005

工作年限

数值输入框

否

1572493554006

备注

多行文本框

否

　　下面是造数据的语句

mysql中根据数组查询 数据库查询数组_$all

mysql中根据数组查询 数据库查询数组_$substr_02

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

　　看一下插入的数据：

mysql中根据数组查询 数据库查询数组_$all_03

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

　　下面看一下查询效果：

mysql中根据数组查询 数据库查询数组_$substr_04

　　说明：从制造假数据的语句中你可以看到“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"]
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        ]))

　　来一张截图，看一下查询结果：

mysql中根据数组查询 数据库查询数组_$all_05

　　注意：这里语句中还用到了“$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"]
                            }
                        }
                    }
                }
            }
        ]))

　　来一张截图，看一下查询结果：

mysql中根据数组查询 数据库查询数组_mongodb_06

　　这里在顺便记录一下在mongodb中数值转字符串，高版本有“$toString”操作符（版本4.0）、“$convert”操作符（版本4.0）……但是低版本的该如何处理，参考链接。这个例子比较简单，就不写制造数据的语句了，直接来查询语句：

db.getCollection('test001').aggregate([
    {
        $addFields: {
            "ageStr": { $substr: [ "$num", 0, -1 ] }
        }
    }
])

　　来一张截图，看一下查询结果：

mysql中根据数组查询 数据库查询数组_数值转字符串_07