Elasticsearch多匹配查询Java代码转换失败及扩展查询需求求助

一、修复现有Java代码的错误 #

1. 核心错误：参数传递错误 #

你的Java代码中构建orgIdQuery时，错误地将VIN号传入了organizationId字段的查询中。organizationId是数值类型，字符串格式的VIN号无法转换为数字，直接引发了NumberFormatException，这是报错的根本原因：

// 错误代码
final QueryBuilder orgIdQuery = QueryBuilders.termsQuery("organizationId", maintenanceLogsSearchDto.getVinNumber());
// 正确代码
final QueryBuilder orgIdQuery = QueryBuilders.termQuery("organizationId", maintenanceLogsSearchDto.getOrganizationId());

另外原DSL使用term单值匹配，Java代码改用termQuery更贴合原逻辑，避免不必要的多值匹配解析。

2. 分页参数计算错误 #

分页起始位置from的计算逻辑错误，正确的公式应为页码 × 每页大小：

// 错误代码
final int from = page <= 0 ? 0 : pageable.getPageSize();
// 正确代码
final int from = page * size;

3. 修正后的完整代码 #

public static SearchRequest buildSearchRequest(final String indexName,
                                               final ElasticSearchDTO dto,
                                               final MaintenanceLogsSearchDto maintenanceLogsSearchDto,
                                               Pageable pageable) {
    try {
        final int page = pageable.getPageNumber();
        final int size = pageable.getPageSize();
        final int from = page * size; // 修正分页起始位置

        SearchRequest searchRequest = new SearchRequest(indexName);
        SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
        BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();

        // 修正参数传递，使用termQuery贴合原DSL逻辑
        final QueryBuilder vinQuery = QueryBuilders.termQuery("vinNumber.keyword", maintenanceLogsSearchDto.getVinNumber());
        final QueryBuilder orgIdQuery = QueryBuilders.termQuery("organizationId", maintenanceLogsSearchDto.getOrganizationId());

        boolQueryBuilder.must(vinQuery);
        boolQueryBuilder.must(orgIdQuery);

        boolQueryBuilder.minimumShouldMatch(1);

        MultiMatchQueryBuilder multiMatchQueryBuilder = new MultiMatchQueryBuilder(dto.getSearchTerm(), 
            "dtcCode", "subSystem", "maintenanceActivity", "description");
        multiMatchQueryBuilder.operator(Operator.AND);
        boolQueryBuilder.should(multiMatchQueryBuilder);

        searchSourceBuilder.query(boolQueryBuilder);

        searchSourceBuilder
                .from(from)
                .size(size)
                .sort(SortBuilders.fieldSort("statsDate")
                              .order(SortOrder.DESC));

        searchRequest.source(searchSourceBuilder);
        return searchRequest;
    } catch (final Exception e) {
        e.printStackTrace();
        return null;
    }
}

二、实现SQL风格的IN查询功能 #

要实现subSystem in ["cabin pressure", "Engine, ABS"] or dtcCode in ["p100", "p200", "p300"]的逻辑，需用bool查询的should子句嵌套terms查询（注意：如果字段是text类型，需用.keyword后缀做精确匹配）：

1. 对应DSL查询 #

GET /maintenance_logs/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "terms": {
            "subSystem.keyword": ["cabin pressure", "Engine, ABS"]
          }
        },
        {
          "terms": {
            "dtcCode.keyword": ["p100", "p200", "p300"]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

2. Java代码实现 #

可单独构建该查询，也可与原有查询逻辑结合：

public static SearchRequest buildInQueryRequest(final String indexName, 
                                               List<String> subSystems, 
                                               List<String> dtcCodes,
                                               Pageable pageable) {
    int page = pageable.getPageNumber();
    int size = pageable.getPageSize();
    int from = page * size;

    SearchRequest searchRequest = new SearchRequest(indexName);
    SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
    BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();

    // 添加subSystem的IN查询
    if (subSystems != null && !subSystems.isEmpty()) {
        boolQuery.should(QueryBuilders.termsQuery("subSystem.keyword", subSystems));
    }

    // 添加dtcCode的IN查询
    if (dtcCodes != null && !dtcCodes.isEmpty()) {
        boolQuery.should(QueryBuilders.termsQuery("dtcCode.keyword", dtcCodes));
    }

    // 至少匹配一个should条件
    boolQuery.minimumShouldMatch(1);

    sourceBuilder.query(boolQuery)
                .from(from)
                .size(size)
                .sort(SortBuilders.fieldSort("statsDate").order(SortOrder.DESC));

    searchRequest.source(sourceBuilder);
    return searchRequest;
}

若需与原有VIN、组织ID查询结合，将上述boolQuery作为子查询加入原有boolQueryBuilder的must或should即可。

内容的提问来源于stack exchange，提问作者Tuhin Subhra Mandal