filter out MongoDB documents based on attributes in an array of objects

Question

We have mongoDB documents representing tasks. Each task contains a list of required skills, and a numeric minimum level for each skill. A person is able to process a task if he has all the required skills with a sufficient level for each.

Task example:

{
  required_skills: [
    {_id: "skill_A", level: 5},
    {_id: "skill_B", level: 2}
  ]
}

A person with skills [{_id: "skill_A", level: 5},{_id: "skill_B", level: 2}] will be able to process tasks with required_skills:

• [{_id: "skill_A", level: 4},{_id: "skill_B", level: 2}]
• [{_id: "skill_A", level: 5}]
• [{_id: "skill_B", level: 1}]

but not tasks with required_skills:

• [{_id: "skill_A", level: 4},{_id: "skill_B", level: 2},{_id: "skill_C", level: 2}] // missing skill_C
• [{_id: "skill_A", level: 6},{_id: "skill_B", level: 2}] // insufficient level on Skill_A

We need a mongo query to be able to find all tasks that are acceptable for a person with given skills. The person skills list will be a parameter of the query.

For performance reasons, we would like to avoid javascript functions in Mongo as well as an incomplete filter which would force us to do some post-processing in java to eliminate irrelevant tasks.

We are looking for something like:

for each task in collection:
  for each skill in task's skills:
   - if skill._id is not in the person's skills, filter out the task
   - if skill.level is greater than the corresponding skill level of the person, filter out the task

return remaining tasks

So far we tried:

{
   "$or":[
      {
         "skills":{
            "$elemMatch":{
               "_id":{
                  "$eq":"skill-2"
               },
               "level":{
                  "$lte":3
               }}}
      },
      {
         "skills":{
            "$elemMatch":{
               "_id":{
                  "$eq":"skill-1"
               },
               "level":{
                  "$lte":3
               }}}}
   ]
}

but with this query a task matches as soon as one of its skills is acceptable. Meaning that the java client application has to implement a second filter to eliminate irrelevant tasks

Answer 1

Since I don't have the actual data, the code is not actually tested. But this might work:

const searchParams = [
    {required_skills:{ $elemMatch: { _id: "Skill_A", level: { $lte: 5 } } }},
    {required_skills:{ $elemMatch: { _id: "Skill_B", level: { $lte: 2 } } }},
    {required_skills:{ $elemMatch: { _id: "Skill_C", level: { $lte: 8 } } }},
]

const tasksQualified = await taskDB.find({
        $or: searchParam
})

The elements of searchParams can be dynamically generated based on your need.

searchParams.psuh({ $elemMatch: { _id: "Skill_D", level: { $lte: 3 } } })

Also, check out this post as well.