如何在单个ES索引中通过ESSQL实现多文档关联合并查询

核心结论 #

Elasticsearch 原生不支持传统关系型数据库中任意跨文档的JOIN关联操作，ES SQL 也没有提供同索引内多文档按字段值匹配合并行的语法。
你之前写的WHERE "A.uniqueid" == "B.uniqueid"逻辑无法实现跨文档匹配：ES的所有查询过滤条件默认是单文档维度执行的，这个语句只会筛选出同一个文档内同时存在A.uniqueid、B.uniqueid两个字段且值相等的文档，不会匹配两个不同文档的字段值。
另外你给出的示例期望结果存在匹配逻辑错误：Doc1的uniqueid为011，和Doc2（A.uniqueid=012）、Doc3（B.uniqueid=012）的关联键值不匹配，实际012键值对应的合并结果应为empid=2, empname=DEF, empposition=developer, empworklocation=WFH。

可行实现方案 #

方案1：写入阶段预合并宽表（生产环境首选） #

Elasticsearch的设计原则是优先在数据写入环节完成关联处理，直接将关联后的数据以单个宽文档的形式存入索引，从根源避免查询时的跨文档匹配开销，这也是性能最高、最稳定的实现方式。
针对你的场景，在数据写入indextest索引前，就以uniqueid类字段为关联键，将同id下的员工基本信息、岗位信息合并到同一个文档中，合并后的文档结构示例：

{
  "index": "indextest",
  "relate_id": "012",
  "empid": 2,
  "empname": "DEF",
  "empposition": "developer",
  "empworklocation": "WFH"
}

存储完成后直接使用普通ES SQL即可查询出你需要的结果：

SELECT empid, empname, empposition AS designation, empworklocation AS worklocation FROM "indextest"

方案2：查询阶段关联（仅适合低频、小数据量场景） #

如果无法在写入阶段做预合并，可以通过原生ES查询DSL实现关联效果，注意这类操作ES SQL不支持，且性能远低于预合并方案，数据量超过百万级时不建议使用：

• 方式1：索引创建时定义join类型字段，将带A.uniqueid的员工基础信息文档设为父文档，带B.uniqueid的岗位信息文档设为同id下的子文档，查询时通过has_child/has_parent语法匹配关联文档，再通过源过滤合并字段返回。
• 方式2：通过脚本聚合按关联id分组，收集同组下的文档字段后合并结果，核心查询DSL示例：

{
  "size": 0,
  "aggs": {
    "group_by_relate_id": {
      "terms": {
        "script": "if(doc['uniqueid'].size()>0) return doc['uniqueid'].value; if(doc['A.uniqueid'].size()>0) return doc['A.uniqueid'].value; return doc['B.uniqueid'].value;",
        "size": 10000
      },
      "aggs": {
        "emp_base": {
          "top_hits": {
            "size": 1,
            "_source": ["empid", "empname"]
          }
        },
        "emp_work": {
          "top_hits": {
            "size": 1,
            "_source": ["empposition", "empworklocation"]
          }
        }
      }
    }
  }
}

查询完成后对聚合返回结果做简单解析，即可拿到合并后的单行数据。

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