Elasticsearch 7.17中实现SQL IN子查询等价操作获取指定部门员工最新项目

我的Elasticsearch文档层级结构如下：

{
  "Department": [
    {
      "Employee": [
        {
          "Projects": []
        }
      ]
    }
  ]
}

文档以项目为存储粒度，需要实现类似SQL的IN查询效果：传入指定部门后，获取该部门下每位员工正在参与的最新项目列表。对应的SQL语句如下：

Select top 1 from Project where employee_id in (select employee_id from department where department_code='Dept')

使用的Elasticsearch版本为7.17。

1. 确认Mapping配置 #

由于文档包含多层嵌套数组，必须将Department和Employee字段设置为nested类型，否则Elasticsearch会自动扁平化数组结构，导致部门、员工、项目的关联关系丢失。示例Mapping如下：

PUT /projects_index
{
  "mappings": {
    "properties": {
      "Department": {
        "type": "nested",
        "properties": {
          "Employee": {
            "type": "nested",
            "properties": {
              "employee_id": {"type": "keyword"},
              "Projects": {
                "type": "nested",
                "properties": {
                  "project_id": {"type": "keyword"},
                  "project_name": {"type": "text"},
                  "create_time": {"type": "date"}
                }
              }
            }
          },
          "department_code": {"type": "keyword"}
        }
      }
    }
  }
}

2. 实现查询DSL #

通过bool查询结合nested查询定位指定部门，再通过聚合获取每位员工的最新项目：

GET /projects_index/_search
{
  "size": 0,
  "query": {
    "nested": {
      "path": "Department",
      "query": {
        "bool": {
          "must": [
            {"term": {"Department.department_code": "Dept"}}
          ]
        },
        "inner_hits": {
          "path": "Department.Employee"
        }
      }
    }
  },
  "aggs": {
    "group_by_employee": {
      "nested": {
        "path": "Department.Employee"
      },
      "aggs": {
        "filter_employees": {
          "filter": {
            "nested": {
              "path": "Department",
              "query": {
                "term": {"Department.department_code": "Dept"}
              }
            }
          },
          "aggs": {
            "latest_project": {
              "top_hits": {
                "size": 1,
                "sort": [{"Department.Employee.Projects.create_time": {"order": "desc"}}],
                "_source": {
                  "includes": ["Department.Employee.employee_id", "Department.Employee.Projects.*"]
                }
              }
            }
          }
        }
      }
    }
  }
}

3. 查询逻辑说明 #

• nested查询：精准匹配目标department_code的部门，确保只处理该部门下的关联数据。
• group_by_employee聚合：按员工维度分组，保证每个员工的结果独立返回。
• top_hits聚合：通过create_time倒序排序，只取每个员工的最新一条项目数据，size:1控制返回数量。

如果你的文档结构是单个项目作为独立文档（即每个文档对应一个项目，直接包含所属员工ID和部门编码），可以简化为以下查询：

GET /projects_index/_search
{
  "size": 0,
  "aggs": {
    "filter_target_dept": {
      "filter": {"term": {"department_code": "Dept"}},
      "aggs": {
        "group_by_employee": {
          "terms": {"field": "employee_id", "size": 100},
          "aggs": {
            "latest_project": {
              "top_hits": {
                "size": 1,
                "sort": [{"create_time": {"order": "desc"}}]
              }
            }
          }
        }
      }
    }
  }
}

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