3

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. This is also working for building nested and/or conditions.

However, I need to also support querying an array of subdocuments (the "Items" array in the example schema).

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

Working example

Top-level properties only

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

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' ] } ] } ] }}

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

Issue

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.

QUESTION

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 query builder

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".

Angular query builder with nested array of objects

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

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" }
            }
          }
        }
      ]
    }
  ]
}
13
  • 1
    Just to clarify: in MongoDB you have two ways to query such array: 1) $elemMatch can be used to find at least one element matching all conditions 2) dot notation like "Items.Title" will try to find any Example Title 1 and all the other criteria will be applied separately so the don't have to apply to the same array element. Is there any way to determine which way of filtering you're trying to apply ? Commented Jul 30, 2019 at 17:39
  • 1
    can you define other property inside const conditions = { "and": "$and", "or": "$or" }; Like adding elemMatch Ex: conditions = { "and": "$and", "or": "$or", "elemMatch": "$elemMatch" };` Commented Aug 1, 2019 at 4:56
  • 1
    @pengz I see what you mean. Unfortuantely there's no good solution here since both condtions for Item appear as separate rules so previous solution works as expected here. Please keep in mind that you can also store a document like this in your database: { Items: { Title: "Example Title 1" } } so the library itself should have a way to distinguish between arrays and nested docs. Commented Aug 5, 2019 at 18:41
  • 1
    @pengz so in previous solution you can ignore all fields where path is specified with dot notation like Items.Title and then you need to loop through your structure and accumulate them into single $elemMatch Commented Aug 6, 2019 at 20:15
  • 1
    Just for clarification: why is your final query syntax is something like { '$eq': [ '$RecordID', 1 ] } rather than { 'RecordID': { '$eq': 1} } or { 'RecordID': 1 }? Commented Aug 7, 2019 at 8:03

1 Answer 1

4

After more research I have a working solution. Thanks to all of the helpful responders who provided insight.

The function takes a query from the Angular query builder module and converts it to a MongoDB query.

Angular query builder

  {
    "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 query result

  {
    "$and": [{
      "RecordType": {
        "$eq": "Item"
      }
    }, {
      "$or": [{
        "Items.Title": {
          "$regex": "book",
          "$options": "i"
        }
      }, {
        "Project": {
          "$in": ["5d0699380a2958e44503acfb", "5d0699380a2958e44503ad2a", "5d0699380a2958e44503ad18"]
        }
      }]
    }]
  }

Code

/**
 * 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;

}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.