Wednesday 31 July 2019

Convert query builder conditions to MongoDB operations including nested array of subdocuments

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.

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". As required, this is also working for nested and/or conditions. However, I need to also support querying a nested 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 nested and/or conditions (working correctly):

{ "condition": "or", "rules": [ { "field": "RecordID", "operator": "=", "value": 1 }, { "condition": "and", "rules": [ { "field": "RecordType", "operator": "=", "value": "Item" } ] } ] }

Here's the query builder output after it has been converted to MongoDB operations (working correctly):

{ '$expr': { '$or': [ { '$eq': [ '$RecordID', 1 ] }, { '$and': [ { '$eq': [ '$RecordType', 'Item' ] } ] } ] }}

Here is the NodeJS query conversion function that converts the angular query object to mongodb operators. As mentioned it works for top level properties but does not to query nested array subdocuments.

Query conversion function:

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);

Issue: This works great for top-level properties such as RecordID and RecordType. However, the problem is this does not work for querying the Items nested arrays of sub-documents.

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.

QUESTION: How can I adapt the "query conversion function" to also support nested arrays of subdocuments? Is there a way to get the $expr to work? I still need to be able to have the nested and/or conditions as shown above.

UPDATE:

Here is an example Angular 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".

Angular query builder with nested array of objects

Here is the query generated by the builder:

{"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"}]}]}



from Convert query builder conditions to MongoDB operations including nested array of subdocuments

No comments:

Post a Comment