MongoDB使用$or和count查找查询

Question

使用$or查询，我在mongodb中遇到了一些小问题，我有一个文档集合，文档的模式如下。它有一个角色和skill_set键，每个角色都有一些相关的技能集。

[
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }
    ],
    "role" : "Project Manager"
},
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }, 
        {
            "skill_name" : "Financial Management"
        }, 
        {
            "skill_name" : "Sales, Marketing and Customer Service "
        }, 
        {
            "skill_name" : "Strategic Input"
        }
    ],
    "role" : "Sr Project Manager"
},
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }, 
        {
            "skill_name" : "Financial Management"
        }, 
        {
            "skill_name" : "Sales, Marketing and Customer Service "
        }, 
        {
            "skill_name" : "Strategic Input"
        }
    ],
    "role" : "Delivery Manager"
},
{
    "skill_set" : [ 
        {
            "skill_name" : "Test Planning"
        }, 
        {
            "skill_name" : "Technlogy Expertise"
        }, 
        {
            "skill_name" : "Team Player"
        }, 
        {
            "skill_name" : "Communication"
        }, 
        {
            "skill_name" : "Design and Architecture"
        }, 
        {
            "skill_name" : "Requirement Understanding"
        }, 
        {
            "skill_name" : "Task Management"
        }
    ],
    "role" : "Tester"
}
]

我想使用角色匹配skill_set搜索文档，我可以使用查询获取这些文档。

{"$or":[{"skill_set.skill_name":"Project Planning"},{"skill_set.skill_name":"Strategic Input"},{"skill_set.skill_name":"Delivery Management"}]},{"_id":0}

用于查询的输出

[
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }
    ],
    "role" : "Project Manager"
},
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }, 
        {
            "skill_name" : "Financial Management"
        }, 
        {
            "skill_name" : "Sales, Marketing and Customer Service "
        }, 
        {
            "skill_name" : "Strategic Input"
        }
    ],
    "role" : "Sr Project Manager"
},
{
    "skill_set" : [ 
        {
            "skill_name" : "Project Planning"
        }, 
        {
            "skill_name" : "Delivery Management"
        }, 
        {
            "skill_name" : "Resource Management"
        }, 
        {
            "skill_name" : "Stakeholder Management"
        }, 
        {
            "skill_name" : "Agile Project Management"
        }, 
        {
            "skill_name" : "Change Management"
        }, 
        {
            "skill_name" : "Financial Management"
        }, 
        {
            "skill_name" : "Sales, Marketing and Customer Service "
        }, 
        {
            "skill_name" : "Strategic Input"
        }
    ],
    "role" : "Delivery Manager"
}
]

在输出中，它是清晰可见的，4个角色中有3个被提取出来。此外，我希望技能集与角色.ie匹配的计数。

在给定的查询中，我使用项目计划或交付管理或策略输入来搜索角色，我希望skill_match_count =2用于角色项目经理，它在他的技能集中有两个匹配项，即我想要类似于下面的内容。

[
{
    "role" : "Project Manager",
    "skill_match_count" : 2 
},
{
    "role" : "Sr Project Manager",
    "skill_match_count" : 3
},
{
    "role" : "Delivery Manager",
    "skill_match_count" : 3
}
]

Answer 1

您需要做的是，使用$filter条件对skill_set数组进行$or，然后对过滤后的数组的$size进行$project：

db.roles.aggregate([
  {
      $project: {
          role: 1,
          filtered_skill_set: {
              $filter: {
                  input: "$skill_set",
                  as: "skill",
                  cond: {
                      $or: [
                          { $eq: ["$$skill.skill_name", "Project Planning"] },
                          { $eq: ["$$skill.skill_name", "Strategic Input"] },
                          { $eq: ["$$skill.skill_name", "Delivery Management"] }
                      ]
                  }
              }
          }
      }
  },
  {
      $project: {
          role: 1,
          skill_match_count: {
              $size: "$filtered_skill_set"
          }
      }
  }
])

这将产生您期望的输出，包括skill_match_count中包含0的角色。如果要排除它们，可以在最后添加另一个$match管道阶段：

{
    $match: {
        skill_match_count: {
            $gt: 0
        }
    }
}