将查询构建器条件转换为MongoDB操作，包括子文档的嵌套数组 [英] Convert query builder conditions to MongoDB operations including nested array of subdocuments
问题描述
我正在客户端Angular 8中构建应用程序，在服务器端使用MongoDB 4/Mongoose 5构建NodeJS 12.我有一个 Angular2查询生成器模块生成的查询. Angular查询构建器对象已发送到服务器.
I am building an application in Angular 8 on the client side and NodeJS 12 with MongoDB 4 / Mongoose 5 on the server side. I have a query generated by the Angular2 query builder module. The Angular query builder object is sent to the server.
我有一个服务器端控制器功能，该功能转换Angular查询反对MongoDB操作.这对于为诸如RecordID和RecordType之类的顶级属性生成查询非常有效.这也适用于构建嵌套和/或条件.
I have a server-side controller function that converts the Angular query object to MongoDB operations. This is working perfectly for generating queries for top-level properties such as RecordID and RecordType. This is also working for building nested and/or conditions.
但是，我还需要支持查询子文档数组(示例架构中的"Items"数组).
However, I need to also support querying an array of subdocuments (the "Items" array in the example schema).
架构
这是我要查询的示例架构:
Here is the example schema I am trying to query:
{
RecordID: 123,
RecordType: "Item",
Items: [
{
Title: "Example Title 1",
Description: "A description 1"
},
{
Title: "Example 2",
Description: "A description 2"
},
{
Title: "A title 3",
Description: "A description 3"
},
]
}
工作示例
仅顶级属性
这是查询生成器输出的示例，仅在顶级属性上具有和/或条件:
Here's an example of the query builder output with and/or conditions on top-level properties only:
{ "condition": "or", "rules": [ { "field": "RecordID", "operator": "=", "value": 1 }, { "condition": "and", "rules": [ { "field": "RecordType", "operator": "=", "value": "Item" } ] } ] }
仅在顶级属性上将其转换为MongoDB操作后，这是查询生成器的输出:
Here's the query builder output after it has been converted to MongoDB operations on top-level properties only:
{ '$expr': { '$or': [ { '$eq': [ '$RecordID', 1 ] }, { '$and': [ { '$eq': [ '$RecordType', 'Item' ] } ] } ] }}
将角度查询对象转换为mongodb运算符.
that converts the angular query object to mongodb operators.
这是现有的查询转换功能
Here is the existing query conversion function that
const conditions = { "and": "$and", "or": "$or" };
const operators = { "=": "$eq", "!=": "$ne", "<": "$lt", "<=": "$lte", ">": "$gt", ">=": "$gte" };
const mapRule = rule => ({
[operators[rule.operator]]: [ "$"+rule.field, rule.value ]
});
const mapRuleSet = ruleSet => {
return {
[conditions[ruleSet.condition]]: ruleSet.rules.map(
rule => rule.operator ? mapRule(rule) : mapRuleSet(rule)
)
}
};
let mongoDbQuery = { $expr: mapRuleSet(q) };
console.log(mongoDbQuery);
问题
该功能仅适用于顶级属性，例如RecordID和RecordType，但是我需要扩展它以支持子文档的Items数组.
The function works for top-level properties only such as RecordID and RecordType, but I need to extend it to support the Items array of subdocuments.
Apparently, to query properties in nested arrays of subdocuments, the $elemMatch operator must be used, based on this related question. However, in my case, the $expr is necessary to build the nested and/or conditions so I can't simply switch to $elemMatch.
问题
如何扩展查询转换功能以也支持$ elemMatch来查询子文档数组?有没有办法让$ expr工作?
How can I extend the query conversion function to also support $elemMatch to query arrays of subdocuments? Is there a way to get the $expr to work?
UI查询生成器
这是带有子文档的嵌套"Items"数组的UI查询构建器.在此示例中，结果应匹配RecordType等于"Item"和Items.Title等于"Example Title 1"或Items.Title包含"Example".
Here is the UI query builder with the nested "Items" array of subdocuments. In this example, the results should match RecordType equals "Item" AND Items.Title equals "Example Title 1" OR Items.Title contains "Example".
这是UI查询构建器生成的输出.注意:field和operator属性值是可配置的.
Here is the output generated by the UI query builder. Note: The field and operator property values are configurable.
{"condition":"and","rules":[{"field":"RecordType","operator":"=","value":"Item"},{"condition":"or","rules":[{"field":"Items.Title","operator":"=","value":"Example Title 1"},{"field":"Items.Title","operator":"contains","value":"Example"}]}]}
更新:我可能已经找到一种查询格式，该查询格式也可以与$elemMatch的嵌套和/或条件一起使用.我必须删除$expr运算符，因为$elemMatch在表达式内部不起作用.我从这个类似问题的答案中获得灵感
UPDATE: I may have found a query format that works with the nested and/or conditions with the $elemMatch as well. I had to remove the $expr operator since $elemMatch does not work inside of expressions. I took inspiration from the answer to this similar question.
这是正在运行的查询.下一步是让我了解如何调整查询构建器转换功能以创建查询.
This is the query that is working. The next step will be for me to figure out how to adjust the query builder conversion function to create the query.
{
"$and": [{
"RecordType": {
"$eq": "Item"
}
},
{
"$or": [{
"RecordID": {
"$eq": 1
}
},
{
"Items": {
"$elemMatch": {
"Title": { "$eq": "Example Title 1" }
}
}
}
]
}
]
}
推荐答案
更多研究之后，我有了一个可行的解决方案.感谢所有提供见解的乐于助人的响应者.
After more research I have a working solution. Thanks to all of the helpful responders who provided insight.
该函数从Angular查询构建器模块中获取查询，并将其转换为MongoDB查询.
The function takes a query from the Angular query builder module and converts it to a MongoDB query.
角度查询生成器
{
"condition": "and",
"rules": [{
"field": "RecordType",
"operator": "=",
"value": "Item"
}, {
"condition": "or",
"rules": [{
"field": "Items.Title",
"operator": "contains",
"value": "book"
}, {
"field": "Project",
"operator": "in",
"value": ["5d0699380a2958e44503acfb", "5d0699380a2958e44503ad2a", "5d0699380a2958e44503ad18"]
}]
}]
}
MongoDB查询结果
MongoDB query result
{
"$and": [{
"RecordType": {
"$eq": "Item"
}
}, {
"$or": [{
"Items.Title": {
"$regex": "book",
"$options": "i"
}
}, {
"Project": {
"$in": ["5d0699380a2958e44503acfb", "5d0699380a2958e44503ad2a", "5d0699380a2958e44503ad18"]
}
}]
}]
}
代码
/**
* Convert a query object generated by UI to MongoDB query
* @param query a query builder object generated by Angular2QueryBuilder module
* @param model the model for the schema to query
* return a MongoDB query
*
*/
apiCtrl.convertQuery = async (query, model) => {
if (!query || !model) {
return {};
}
const conditions = { "and": "$and", "or": "$or" };
const operators = {
"=": "$eq",
"!=": "$ne",
"<": "$lt",
"<=": "$lte",
">": "$gt",
">=": "gte",
"in": "$in",
"not in": "$nin",
"contains": "$regex"
};
// Get Mongoose schema type instance of a field
const getSchemaType = (field) => {
return model.schema.paths[field] ? model.schema.paths[field].instance : false;
}
// Map each rule to a MongoDB query
const mapRule = (rule) => {
let field = rule.field;
let value = rule.value;
if (!value) {
value = null;
}
// Get schema type of current field
const schemaType = getSchemaType(rule.field);
// Check if schema type of current field is ObjectId
if (schemaType === 'ObjectID' && value) {
// Convert string value to MongoDB ObjectId
if (Array.isArray(value)) {
value.map(val => new ObjectId(val));
} else {
value = new ObjectId(value);
}
// Check if schema type of current field is Date
} else if (schemaType === 'Date' && value) {
// Convert string value to ISO date
console.log(value);
value = new Date(value);
}
console.log(schemaType);
console.log(value);
// Set operator
const operator = operators[rule.operator] ? operators[rule.operator] : '$eq';
// Create a MongoDB query
let mongoDBQuery;
// Check if operator is $regex
if (operator === '$regex') {
// Set case insensitive option
mongoDBQuery = {
[field]: {
[operator]: value,
'$options': 'i'
}
};
} else {
mongoDBQuery = { [field]: { [operator]: value } };
}
return mongoDBQuery;
}
const mapRuleSet = (ruleSet) => {
if (ruleSet.rules.length < 1) {
return;
}
// Iterate Rule Set conditions recursively to build database query
return {
[conditions[ruleSet.condition]]: ruleSet.rules.map(
rule => rule.operator ? mapRule(rule) : mapRuleSet(rule)
)
}
};
let mongoDbQuery = mapRuleSet(query);
return mongoDbQuery;
}
这篇关于将查询构建器条件转换为MongoDB操作，包括子文档的嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
