Spring Data JPA IN clause returning more than expected values, when any element of list, to be passed is having hyphen in it

user_11009000 0
Viewed 83

While fetching records using IN clause, the below query is returning more than expected values.

List`<Object>` findAllByCameraIdIn(List`<String>` cameraIds);

I have records associated with two cameras in elastic db - [uk05-smoking-shelter-carpark, uk05-stairway-in]

If List cameraIds = ["uk05-smoking-shelter-carpark"], it's giving values associated with camera -> uk05-stairway-in also (both cameras), Any idea/suggestion why this is happing ?

Even if I'm making db call to filter the records, expected result should have been only 7, corresponding to uk05-smoking-shelter-carpark but it is giving me results for uk05-stairway-in also.

enter image description here

My Findings

When I replaced the - with _ for few records i.e., (uk05-smoking-shelter-carpark with uk05_smoking_shelter_carpark) in the cameraId, the query is working fine.

enter image description here

I believe the query starts searching for all the records with the given value but once it enconters - , it's ignoring all the letters after the - . Any suggestion or insights why it is like this?

spring-data-jpa elasticsearch spring-boot
1 Answers

Elasticsearch uses a standard analyzer if no analyzer is specified. Assuming cameraId field is of text type, so uk05-smoking-shelter-carpark will get tokenized into

{
  "tokens": [
    {
      "token": "uk05",
      "start_offset": 0,
      "end_offset": 4,
      "type": "<ALPHANUM>",
      "position": 0
    },
    {
      "token": "smoking",
      "start_offset": 5,
      "end_offset": 12,
      "type": "<ALPHANUM>",
      "position": 1
    },
    {
      "token": "shelter",
      "start_offset": 13,
      "end_offset": 20,
      "type": "<ALPHANUM>",
      "position": 2
    },
    {
      "token": "carpark",
      "start_offset": 21,
      "end_offset": 28,
      "type": "<ALPHANUM>",
      "position": 3
    }
  ]
}

So when searching for "uk05-smoking-shelter-carpark" will match all the documents that have any of the tokens shown above.

  1. If you want to return the documents that match exactly with the search query then you need to change the data type of cameraId to keyword type

  2. OR if you have not explicitly defined any mapping then you need to add .keyword to the cameraId field. This uses the keyword analyzer instead of the standard analyzer (notice the ".keyword" after cameraId field).

  3. It is better to use a term query if you are searching for an exact term match.

Search Query using match query

{
  "query":{
    "match":{
      "cameraId.keyword":"uk05_smoking_shelter_carpark"
    }
  }
}

Search Query using term query

{
  "query":{
    "term":{
      "cameraId.keyword":"uk05_smoking_shelter_carpark"
    }
  }
}

When you replace - with _, i.e "uk05_smoking_shelter_carpark", this will get tokenized into

GET /_analyze
{
  "analyzer" : "standard",
  "text" : "uk05_smoking_shelter_carpark"
}

Token generated will be

{
  "tokens": [
    {
      "token": "uk05_smoking_shelter_carpark",
      "start_offset": 0,
      "end_offset": 28,
      "type": "<ALPHANUM>",
      "position": 0
    }
  ]
}

In this case, the search query will only return the documents that match uk05_smoking_shelter_carpark

user_103487580
Related