Elasticsearch 6.2.4嵌套文档聚合查询构造咨询

Hey there! Since you're new to Elasticsearch queries and aggregations, let's break down how to solve your two requirements step by step, tailored for Elasticsearch 6.2.4.

1. 查询员工数量最多的部门 #

思路 #

Each document in your index represents a department, with an employee nested array holding all its staff. We need to count how many employees are in each department, then sort those counts to find the department with the largest number of staff.

We'll use a terms aggregation to group by department ID, then a value_count sub-aggregation to count unique employee IDs (since each empId is unique, this gives us the total number of employees per department). Finally, we'll sort the results by this count in descending order to get the top department.

Query DSL #

GET /company/data/_search
{
  "size": 0,
  "aggs": {
    "group_by_department": {
      "terms": {
        "field": "deptId",
        "size": 10,
        "order": {
          "total_employees": "desc"
        }
      },
      "aggs": {
        "department_name": {
          "terms": {
            "field": "deptName",
            "size": 1
          }
        },
        "total_employees": {
          "value_count": {
            "field": "employee.empId"
          }
        }
      }
    }
  }
}

结果说明 #

• size: 0: We don't need the raw document results, just the aggregation data.
• group_by_department: Groups results by deptId, sorted by the total_employees count in descending order.
• total_employees: Counts the number of unique empId values in the department's employee array—this is the total number of staff.
• department_name: Grabs the corresponding department name so our results are more readable.

2. 查询在最多部门中出现的员工 #

思路 #

To find employees who appear in the most departments, we need to count how many unique departments each employee is part of.

First, we'll use a nested aggregation to dive into the employee array. Then we'll group by empId, use reverse_nested to jump back to the parent department document, and count the number of unique departments for each employee with a cardinality aggregation. Finally, we'll sort these counts to find the employees with the most department appearances.

Query DSL #

GET /company/data/_search
{
  "size": 0,
  "aggs": {
    "access_employees": {
      "nested": {
        "path": "employee"
      },
      "aggs": {
        "group_by_employee": {
          "terms": {
            "field": "employee.empId",
            "size": 10,
            "order": {
              "department_count": "desc"
            }
          },
          "aggs": {
            "employee_name": {
              "terms": {
                "field": "employee.empName",
                "size": 1
              }
            },
            "back_to_departments": {
              "reverse_nested": {},
              "aggs": {
                "department_count": {
                  "cardinality": {
                    "field": "deptId"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

结果说明 #

• access_employees: Uses the nested aggregation to work with the individual entries in the employee array.
• group_by_employee: Groups results by empId, sorted by the department_count in descending order.
• back_to_departments: Uses reverse_nested to return to the parent department document, then department_count uses cardinality to count how many unique departments this employee belongs to.
• employee_name: Adds the employee's name to the results for clarity.

内容的提问来源于stack exchange，提问作者onlinejava