I am newbie at elasticsearch. Using elasticsearch 7.8.1 for some custom search for my application.

Here is the sample dataset. The search that need to happen is something like this:

select * from maintenance_logs where vinNumber = "xyz" and organizationId = 1 and dtcCode like %p101% or subSystem like %p101% or description like %p101%;

Here is the document stored:

GET /maintenance_logs/_search


{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "maintenance_logs",
        "_type" : "_doc",
        "_id" : "41a47230-02d1-11ed-a8f8-813988188fd2",
        "_score" : 1.0,
        "_source" : {
          "_class" : "com.futuresense.autonostix360.domain.search.MaintenanceLog",
          "id" : "41a47230-02d1-11ed-a8f8-813988188fd2",
          "maintenanceActivity" : "test103",
          "vinNumber" : "DH34ASD7SDFF84742",
          "organizationId" : 1,
          "partitionYear" : "2022",
          "dtcCode" : "",
          "subSystem" : "",
          "description" : "",
          "odometer" : 91000,
          "statsDate" : "2022-07-13"
        }
      },
      {
        "_index" : "maintenance_logs",
        "_type" : "_doc",
        "_id" : "5fac7720-033d-11ed-97e1-a3441dab3d6a",
        "_score" : 1.0,
        "_source" : {
          "_class" : "com.futuresense.autonostix360.domain.search.MaintenanceLog",
          "id" : "5fac7720-033d-11ed-97e1-a3441dab3d6a",
          "maintenanceActivity" : "test103",
          "vinNumber" : "DH34ASD7SDFF84742",
          "organizationId" : 1,
          "partitionYear" : "2022",
          "dtcCode" : "D101",
          "subSystem" : "ac vent",
          "description" : "ac vent replaced",
          "odometer" : 91000,
          "statsDate" : "2022-07-14"
        }
      }
    ]
  }
}

This is how my Document looks:

@Document(indexName = "maintenance_logs", createIndex = true)
public class MaintenanceLog {

    @Id
    private String id;

    private String maintenanceActivity;

    private String vinNumber;

    private Integer organizationId;

    private String partitionYear;

    private String dtcCode;

    private String subSystem;

    private String description;

    private Integer odometer;
}

Here is my query: The intention is, I have a search bar where lets say I typed p101. Then it should look through all the documents. do exact match on vin_number and organizatinid, and then whatever matches (partial match eg mysql like query) from any one of these attributes dtcCode or subSystem or maintenanceActivity or description.

GET /maintenance_logs/_search
{
  "query": {
    "bool" : {
      "must" : [
        { "term" : { "vinNumber" : "DH34ASD7SDFF84742" } },
        { "term" : { "organizationId" : 1 } }
      ],
      "should" : [
        { "term" : { "dtcCode": "p101*" } },
        { "term" : { "subSystem" : "p101*" }}, 
        { "term" : { "maintenanceActivity" : "p101*" }},
        { "term" : { "description" : "p101*" }}
      ],
      "minimum_should_match" : 1,
      "boost" : 1.0
    }
  }
}

CodePudding user response：

You have two issues with your query:

One is you are using term query instead of wildcard query pattern. Second is you are trying term query text type of field for vinNumber field.

To resolve this issue, You need to use wildcard query instead of term query and you need to use vinNumber.keyword insted of vinNumber (considering you are using multi type field for vinNumber as text and keyword both). Please check below query:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "vinNumber.keyword": "DH34ASD7SDFF84742"
          }
        },
        {
          "term": {
            "organizationId": 1
          }
        }
      ],
      "minimum_should_match": 1, 
      "should": [
        {
          "wildcard": {
            "dtcCode": {
              "value": "d10*"
            }
          }
        },
        {
          "wildcard": {
            "subSystem": {
              "value": "p101*"
            }
          }
        },
        {
          "wildcard": {
            "maintenanceActivity": {
              "value": "p101*"
            }
          }
        },
        {
          "wildcard": {
            "description": {
              "value": "p101*"
            }
          }
        }
      ]
    }
  }
}

Also, you need to set "minimum_should_match": 1 as your query have AND condition with vinNumberand organizationId.

CodePudding user response：

Term queries are used for exact text matching. If you need to do the partial search you can either use Wildcard Query, Regexp Query or Query String

If you are using the default mapping then you need to modify your query as :

{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "vinNumber.keyword": "DH34ASD7SDFF84742"
                    }
                },
                {
                    "term": {
                        "organizationId": 1
                    }
                }
            ],
            "should": [
                {
                    "query_string": {
                        "query": "*p101*",
                        "fields": [
                            "dtcCode"
                        ]
                    }
                },
                {
                    "query_string": {
                        "query": "*p101*",
                        "fields": [
                            "subSystem"
                        ]
                    }
                },
                {
                    "query_string": {
                        "query": "*p101*",
                        "fields": [
                            "maintenanceActivity"
                        ]
                    }
                },
                {
                    "query_string": {
                        "query": "*p101*",
                        "fields": [
                            "description"
                        ]
                    }
                }
            ],
            "minimum_should_match": 1
        }
    }
}

Note : If you need to perform a partial search in such a way that, the text matches from the beginning of the value of the fields dtcCode,subSystem, etc. then you can simply go with Prefix Query as well.