Elasticsearch 8.8.2 DSL查询未返回预期结果问题排查

在Elasticsearch 8.8.2中，针对candidature索引的嵌套路径applications执行两个查询时出现异常：目标是找出workexp或summary包含Tableau相关内容且forreqid为FH-REQ-3的文档，但符合条件的文档未出现在Query 1（预期匹配的查询）结果中，反而出现在Query 2（排除目标内容的查询）结果里。

问题详情 #

执行的查询语句 #

Query 1（预期匹配目标文档）

{"nested":{"path":"applications","query":{"bool":{"must":[{"simple_query_string":{"default_operator":"and","fields":["applications.workexp"],"query":"(Tableau) Or (Tableau) Or (Tableau workbooks)"}},{"simple_query_string":{"default_operator":"and","fields":["applications.summary"],"query":"(Tableau) Or (Tableau) Or (Tableau workbooks)"}},{"match":{"applications.forreqid":{"query":"FH-REQ-3"}}}]}}}

Query 2（排除目标内容的查询）

{"nested":{"path":"applications","query":{"bool":{"must":[{"match":{"applications.forreqid":{"query":"FH-REQ-3"}}}],"must_not":[{"simple_query_string":{"default_operator":"and","fields":["applications.workexp"],"query":"(Tableau) Or (Tableau) Or (Tableau workbooks)"}},{"simple_query_string":{"default_operator":"and","fields":["applications.summary"],"query":"(Tableau) Or (Tableau) Or (Tableau workbooks)"}}]}}}

预期匹配的文档 #

{"createdby":"abc@soething.com","applications":[{"applnid":"yy","summary":"top and Tableau Server.· Involved in dashboard test cases creation and execution, prepared the understanding and function/ process flow documents on various dashboards for the end users.· Created technical specifications document as well as functional documents in support of the user requirements.· Generate Tableau reports to analyze data from multiple data sources like Oracle, SQL Server, Excel, Flat Files, etc · Experienced in designing customized interactive dashboards in Tableau using Marks, Action, filters, parameter and calculations · Having experience in Tableau Desktop Creating Sets, Group, Sort, Parameter, Quick filters, Context Filters, Data blending, Joins and Calculations etc.Experience Details · Educational Detai","workexp":"Procurement of billing the project and Project lead · The solution involves in creating dashboards and stories that depict different levels and stages.· The very first is a managerial dashboard to give quick overview of the Categories and their overview among different geographical areas, trends and comparisons using Map Charts, Pies, Stacked Bars, Scatter Plots etc.· The second one concentrates more on slicing and dicing the inventory and sales data using Dual Axis Charts, Various line charts, Waterfall charts etc.· The last one is a blend of Calculated Fields, Table Calculations and a bit of LODs to answer different types of the requirements of the client. Role : Tableau Developer Revenue Growth in % · Used Filters to know Department wise Sales and their Cost for Particular Periods and draft various charts using Show Me in Tableau Desktop.","education":null,"certtrainings":null,"gaps":null,"skilltags":null,"forreqid":"FH-REQ-3","uploadedbyuser":null}]}

Java查询代码 #

// 构造查询条件
Query workExQuery = SimpleQueryStringQuery.of(q -> q.query(finalQuery)
                .fields(Arrays.asList("applications.workexp")).defaultOperator(Operator.And))._toQuery();
queries.add(workExQuery);

Query summaryQuery = SimpleQueryStringQuery.of(q -> q.query(finalQuery)
                .fields(Arrays.asList("applications.summary")).defaultOperator(Operator.And))._toQuery();
queries.add(summaryQuery);

// Query 1构造
Query query = Query.of(q -> q.nested(p -> p.path("applications").query(b -> b.bool(bq -> bq.must(queries)))));

// Query 2构造
Query query = Query.of(q -> q.nested(p -> p.path("applications").query(b -> b.bool(bq -> bq.mustNot(queries)))));

// 执行查询
esClient.search(s -> s.index("candidature").query(query), Someclass.class);

Java字段映射 #

@Field(type = FieldType.Keyword, name = "applnid")
private String applnid;

@Field(type = FieldType.Text, name = "summary")
private String summary;

@Field(type = FieldType.Text, name = "skills")
private String skills;

@Field(type = FieldType.Text, name = "employers")
private String employers;

@Field(type = FieldType.Integer, name = "totalexperienceinyears")
private String totalexperienceinyears;

@Field(type = FieldType.Text, name = "workexp")
private String workexp;

@Field(type = FieldType.Text, name = "education")
private String education;

@Field(type = FieldType.Text, name = "certtrainings")
private String certtrainings;

@Field(type = FieldType.Integer, name = "gaps")
private String gaps;

@Field(type = FieldType.Text, name = "skilltags")
private String skilltags;

@Field(type = FieldType.Keyword, name = "forreqid")
private String forreqid;

@Field(type = FieldType.Keyword, name = "uploadedbyuser")
private String uploadedbyuser;

Elasticsearch索引映射 #

{"candidature":{"aliases":{},"mappings":{"properties":{"_class":{"type":"keyword","index":false,"doc_values":false},"applications":{"type":"nested","include_in_parent":true,"properties":{"_class":{"type":"keyword","index":false,"doc_values":false},"applnid":{"type":"keyword"},"certtrainings":{"type":"text"},"education":{"type":"text"},"employers":{"type":"text"},"forreqid":{"type":"keyword"},"gaps":{"type":"integer"},"skills":{"type":"text"},"skilltags":{"type":"text"},"summary":{"type":"text"},"totalexperienceinyears":{"type":"integer"},"uploadedbyuser":{"type":"keyword"},"workexp":{"type":"text"}}},"candidateId":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"contactnumber":{"type":"keyword"},"createdby":{"type":"keyword"},"email":{"type":"keyword"},"fororg":{"type":"keyword"},"name":{"type":"text"}}},"settings":{"index":{"routing":{"allocation":{"include":{"_tier_preference":"data_content"}}},"refresh_interval":"1s","number_of_shards":"1","provided_name":"candidature","creation_date":"1694872335334","store":{"type":"fs"},"number_of_replicas":"1","uuid":"9cRKu-TLRsGfwuKn2UTbWg","version":{"created":"8080299"}}}}}

问题原因 #

1. Simple Query String语法错误：simple_query_string中逻辑运算符需用小写，大写Or会被识别为普通文本。同时设置了default_operator: and，导致查询逻辑变成Tableau AND Tableau AND (Tableau workbooks)，而非预期的或逻辑，无法匹配目标文档。
2. 嵌套条件过严：Query 1要求同一个applications嵌套对象同时满足workexp包含Tableau、summary包含Tableau、forreqid为FH-REQ-3。但因查询语法错误，其中一个字段的查询未命中，导致整个嵌套对象不满足must条件，文档无法被返回。

解决方案 #

修正Query 1（JSON格式） #

调整simple_query_string的语法，将workexp和summary的查询放在should中，设置minimum_should_match: 1实现“或”逻辑：

{
  "nested": {
    "path": "applications",
    "query": {
      "bool": {
        "must": [
          {
            "match": {
              "applications.forreqid": "FH-REQ-3"
            }
          }
        ],
        "should": [
          {
            "simple_query_string": {
              "default_operator": "or",
              "fields": ["applications.workexp"],
              "query": "Tableau OR \"Tableau workbooks\""
            }
          },
          {
            "simple_query_string": {
              "default_operator": "or",
              "fields": ["applications.summary"],
              "query": "Tableau OR \"Tableau workbooks\""
            }
          }
        ],
        "minimum_should_match": 1
      }
    }
  }
}

修正Java代码 #

对应调整查询构造逻辑，用should组合字段查询并设置最小匹配数：

// 构造查询条件
Query workExQuery = SimpleQueryStringQuery.of(q -> q.query("Tableau OR \"Tableau workbooks\"")
        .fields(Arrays.asList("applications.workexp")).defaultOperator(Operator.Or))._toQuery();

Query summaryQuery = SimpleQueryStringQuery.of(q -> q.query("Tableau OR \"Tableau workbooks\"")
        .fields(Arrays.asList("applications.summary")).defaultOperator(Operator.Or))._toQuery();

// Query 1构造：must放forreqid，should放两个字段查询，设置minimum_should_match为1
Query query = Query.of(q -> q.nested(p -> p.path("applications").query(b -> b.bool(bq -> 
        bq.must(m -> m.match(mm -> mm.field("applications.forreqid").query("FH-REQ-3")))
        .should(workExQuery)
        .should(summaryQuery)
        .minimumShouldMatch("1")
))));

// 执行查询
esClient.search(s -> s.index("candidature").query(query), Someclass.class);

额外建议 #

如果需求是workexp和summary同时包含Tableau，可保持must组合，但需确保simple_query_string的语法正确；也可直接用match查询替代simple_query_string，避免语法错误：

{
  "match": {
    "applications.workexp": "Tableau"
  }
}

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