KnowledgeSeeker001
Reputation: 638
Unable to search documents in elastic search with capital letters with terms query builder.?
Hi I am having a spring boot application which connects to elastic search . I have some junit tests which starts an embedded elastic search instance (6.6.1) , creates an index and insert some documents under it . Some of my junit tests fails while performing a search requests on the index because I am using term query.
I am using elastic search version 6.6.1 . the following is my index mapping.
{
"doc": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text"
},
"account_id": {
"type": "keyword"
},
"campaign_id": {
"type": "keyword"
},
"delivery_ts": {
"type": "date",
"format": "epoch_millis"
},
"submission_ts": {
"type": "date",
"format": "epoch_millis"
},
"flight_id": {
"type": "keyword"
},
"inventory": {
"type": "keyword"
},
"msg_text": {
"type": "keyword"
},
"nof_segments": {
"type": "keyword"
},
"o_error": {
"type": "keyword"
},
"recipient": {
"type": "text"
},
"sender": {
"type": "keyword"
},
"status": {
"type": "keyword"
},
"campaign_name": {
"type": "keyword"
},
"flight_name": {
"type": "keyword"
}
}
}
}
the following is my index data when i give a curl request
curl -X GET "http://10.10.9.1:9200/mep*/doc/_search?pretty" -H 'Content-Type: application/json' -d'
{
"query": {
"match_all": {}
}
}
'
my index data
{
"took": 11,
"timed_out": false,
"_shards": {
"total": 10,
"successful": 10,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1016513,
"max_score": 1,
"hits": [
{
"_index": "mep-reports-2019.09.11",
"_type": "doc",
"_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837500",
"_score": 1,
"_source": {
"account_id": "270d13e6-2f4f-4d51-99d5-92ffba5f0cb6",
"inventory": "SMS",
"flight_name": "test flight 001",
"status": "ENROUTE",
"msg_text": "Test !!!!!!!!!!!!!!1 elastic search",
"flight_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c",
"submission_ts": "1568197286",
"recipient": "353899837500",
"o_error": null,
"nof_segments": "-1",
"campaign_id": "0fae8662-bee9-46ac-9b3e-062f4ba55966",
"campaign_name": "Index search petri11",
"@version": "1",
"sender": "800111",
"delivery_ts": "0",
"@timestamp": "2019-09-11T10:21:26.000Z"
}
},
{
"_index": "mep-reports-2019.09.11",
"_type": "doc",
"_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837503",
"_score": 1,
"_source": {
"account_id": "270d13e6-2f4f-4d51-99d5-92ffba5f0cb6",
"inventory": "SMS",
"flight_name": "test flight 001",
"status": "ENROUTE",
"msg_text": "Test !!!!!!!!!!!!!!1 elastic search",
"flight_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c",
"submission_ts": "1568197286",
"recipient": "353899837503",
"o_error": null,
"nof_segments": "-1",
"campaign_id": "0fae8662-bee9-46ac-9b3e-062f4ba55966",
"campaign_name": "Index search petri11",
"@version": "1",
"sender": "800111",
"delivery_ts": "0",
"@timestamp": "2019-09-11T10:21:26.000Z"
}
},
{
"_index": "mep-reports-2019.09.11",
"_type": "doc",
"_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c-353899837502",
"_score": 1,
"_source": {
"account_id": "270d13e6-2f4f-4d51-99d5-92ffba5f0cb6",
"inventory": "SMS",
"flight_name": "test flight 001",
"status": "ENROUTE",
"msg_text": "Test !!!!!!!!!!!!!!1 elastic search",
"flight_id": "68e8e03f-baf8-4bfc-a920-58e26edf835c",
"submission_ts": "1568197286",
"recipient": "353899837502",
"o_error": null,
"nof_segments": "-1",
"campaign_id": "0fae8662-bee9-46ac-9b3e-062f4ba55966",
"campaign_name": "Index search petri11",
"@version": "1",
"sender": "800111",
"delivery_ts": "0",
"@timestamp": "2019-09-11T10:21:26.000Z"
}
}
]
}
}
However When i make a search on the inventory or status field it fails because I am using terms query (TermsQueryBuilder) while building my search request.
class SearchRequestBuilder {
private SearchRequest searchRequest;
private SearchSourceBuilder searchSourceBuilder;
private BoolQueryBuilder boolQueryBuilder;
SearchRequestBuilder(String elasticSearchIndex) {
this.searchRequest = new SearchRequest(elasticSearchIndex);
this.searchRequest.indicesOptions(IndicesOptions.lenientExpandOpen());
this.searchSourceBuilder = new SearchSourceBuilder();
this.searchRequest.source(searchSourceBuilder);
this.boolQueryBuilder = QueryBuilders.boolQuery();
this.searchSourceBuilder.query(this.boolQueryBuilder);
}
SearchRequestBuilder from(Integer pageNumber) {
this.searchSourceBuilder.from(pageNumber);
return this;
}
SearchRequestBuilder size(Integer pageSize) {
this.searchSourceBuilder.size(pageSize);
return this;
}
SearchRequestBuilder timeout(long duration, TimeUnit timeUnit) {
this.searchSourceBuilder.timeout(new TimeValue(duration, TimeUnit.SECONDS));
return this;
}
SearchRequestBuilder range(ZonedDateTime startDate, ZonedDateTime endDate) {
RangeQueryBuilder startRangeQueryBuilder = QueryBuilders.rangeQuery("@timestamp").gte(startDate);
startRangeQueryBuilder.format("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
boolQueryBuilder.must(startRangeQueryBuilder);
RangeQueryBuilder endRangeQueryBuilder = QueryBuilders.rangeQuery("@timestamp").lte(endDate.plusDays(1l));
boolQueryBuilder.must(endRangeQueryBuilder);
endRangeQueryBuilder.format("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
return this;
}
SearchRequestBuilder msisdn(String msisdn) {
if (msisdn != null) {
if (msisdn.indexOf("*") >= 0) {
WildcardQueryBuilder msisdnWildCardQueryBuilder = new WildcardQueryBuilder("recipient", msisdn);
boolQueryBuilder.must(msisdnWildCardQueryBuilder);
} else {
MatchQueryBuilder matchQueryBuilder = new MatchQueryBuilder("recipient", msisdn);
boolQueryBuilder.must(matchQueryBuilder);
}
}
return this;
}
SearchRequestBuilder accountId(UUID accountId) {
if (accountId != null) {
MatchQueryBuilder matchQueryBuilder = new MatchQueryBuilder("account_id", accountId.toString());
boolQueryBuilder.must(matchQueryBuilder);
}
return this;
}
SearchRequestBuilder campaignId(UUID campaignId) {
if (campaignId != null) {
MatchQueryBuilder matchQueryBuilder = new MatchQueryBuilder("campaign_id", campaignId.toString());
boolQueryBuilder.must(matchQueryBuilder);
}
return this;
}
SearchRequestBuilder flightId(UUID flightId) {
if (flightId != null) {
MatchQueryBuilder matchQueryBuilder = new MatchQueryBuilder("flight_id", flightId.toString());
boolQueryBuilder.must(matchQueryBuilder);
}
return this;
}
**SearchRequestBuilder cdrStatus(List<String> cdrStatus) {
if (cdrStatus != null && cdrStatus.size() > 0) {
TermsQueryBuilder termsQueryBuilder = new TermsQueryBuilder("status", cdrStatus);
boolQueryBuilder.must(termsQueryBuilder);
}
return this;
}**
**SearchRequestBuilder inventoryCode(List<String> inventoryCode) {
if (inventoryCode != null && inventoryCode.size() > 0) {
TermsQueryBuilder termsQueryBuilder = new TermsQueryBuilder("inventory", inventoryCode);
boolQueryBuilder.must(termsQueryBuilder);
}**
return this;
}
SearchRequestBuilder sort() {
searchSourceBuilder.sort(new FieldSortBuilder("@timestamp").order(SortOrder.DESC));
return this;
}
SearchRequestBuilder scroll(TimeValue value) {
searchRequest.scroll(new Scroll(value));
return this;
}
SearchRequestBuilder fetchSourceContext(boolean fetchSource, String[] includes, String[] excludes) {
FetchSourceContext fetchContext = new FetchSourceContext(fetchSource, includes, excludes);
this.searchSourceBuilder.fetchSource(fetchContext);
return this;
}
SearchRequest build() {
return this.searchRequest;
}
}
the followign is the code snippet from junit that starts the embedded elastic search and runs a junit test.
@RunWith(SpringRunner.class)
@SpringBootTest(classes = PrimecastApp.class)
public class MessageHistoryReportingResourceIntTest {
embeddedElastic = EmbeddedElastic.builder().withElasticVersion("6.6.1")
.withSetting(PopularProperties.HTTP_PORT, 57457)
.withSetting(PopularProperties.CLUSTER_NAME, "my_cluster").withStartTimeout(5, TimeUnit.MINUTES)
.withIndex(indexName, IndexSettings.builder().withType("doc", getJsonResourceAsStream()).build())
.build().start();
ObjectMapper objMapper = new ObjectMapper();
objMapper.registerModule(new JavaTimeModule());
objMapper.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS, false);
objMapper.configure(SerializationFeature.WRITE_DATE_TIMESTAMPS_AS_NANOSECONDS, false);
List<MessageHistory> messageHistories = objMapper.readValue(getMessageHistoryResourceAsStream(),
objMapper.getTypeFactory().constructCollectionType(List.class, MessageHistory.class));
BulkRequest request = new BulkRequest();
Collections.sort(messageHistories, Comparator.comparing(MessageHistory::getTimeStamp));
for (int i = 0; i < messageHistories.size(); i++) {
request.add(new IndexRequest("mep-reports", "doc", String.valueOf(i))
.source(objMapper.writeValueAsString(messageHistories.get(i)), XContentType.JSON));
}
request.timeout(TimeValue.timeValueMinutes(5));
client = new RestHighLevelClient(RestClient.builder(new HttpHost("localhost", 57457, "http")));
BulkResponse response = client.bulk(request, RequestOptions.DEFAULT);
@Test
public void testSearchMessageHistoryByRangeAccountCampaignFlightRecipientInventoryAndStatus() throws Exception {
this.restMvc
.perform(MockMvcRequestBuilders.get("/api/message-history")
.param("startDate", "2019-08-31T23:00:00.000Z").param("endDate", "2019-09-11T07:06:26.287Z")
.param("pageNumber", "0").param("pageSize", "10")
.param("accountId", "a56f7e14-20f9-40e6-90c6-10604140ac5f")
.param("campaignId", "6f2abca3-b46d-43f3-91be-3278a8dd7dc0")
.param("flightId", "92348fa1-ca6c-456a-b3b2-85fba2d2deed").param("msisdn", "420736408281")
.param("inventoryCode", "SMS").param("cdrStatus", "ENROUTE")
)
.andDo(print()).andExpect(content().contentType(MediaType.APPLICATION_JSON_UTF8_VALUE))
.andExpect(status().isOk()).andExpect(jsonPath("$.pageSize").value(10))
.andExpect(jsonPath("$.pageNumber").value(0))
.andExpect(jsonPath("$.totalHits").value(Matchers.greaterThan(0)))
.andExpect(jsonPath("$.messageHistories.length()").value(Matchers.greaterThan(0)))
.andExpect(jsonPath("$.messageHistories[0].account_id")
.value(Matchers.equalTo("a56f7e14-20f9-40e6-90c6-10604140ac5f")))
.andExpect(jsonPath("$.messageHistories[0].campaign_id")
.value(Matchers.equalTo("6f2abca3-b46d-43f3-91be-3278a8dd7dc0")))
.andExpect(jsonPath("$.messageHistories[0].campaign_id")
.value(Matchers.equalTo("6f2abca3-b46d-43f3-91be-3278a8dd7dc0")))
.andExpect(jsonPath("$.messageHistories[0].flight_id")
.value(Matchers.equalTo("92348fa1-ca6c-456a-b3b2-85fba2d2deed")))
.andExpect(jsonPath("$.messageHistories[0].recipient").value(Matchers.equalTo("420736408281")))
.andExpect(jsonPath("$.messageHistories[0].inventory").value(Matchers.equalTo("SMS")))
.andExpect(jsonPath("$.messageHistories[0].status").value(Matchers.equalTo("ENROUTE")));
}
}
The above tests fails. However if i rewrite my test it will work like as below
.param("inventoryCode", "sms").param("cdrStatus", "enroute")
The internet says I need to change my index mappings file. I tried a couple of options and it didnt work.
Upvotes: 1
Views: 309
Answers (1)
LeBigCat
Reputation: 1770
You index your data using KEYWORD, these keyword are case sensitive. So or you keep your data and request them using .upper in your application OR you reindex them (as text or if you want to optimise (and more robutness), you can index your field to be unsentive (https://discuss.elastic.co/t/exact-match-with-case-insensitivity/89582/8 show you how to do it)
"inventory": {
"type": "keyword"
},
"status": {
"type": "keyword"
},
Upvotes: 1
Related Questions
- Only getting results when elasticsearch is case sensitive
- How to search case insensitive using wildcard elastic search java
- How to implement "lowercase" in an ElasticSearch query?
- Elasticsearch query not working on fields with capital letters
- Elasticsearch lowercase filter search
- Elastic Search wildcard query not working with case insensitive ( for lower case)
- Elasticsearch 6.2: terms query require lowercase input when searching on keyword
- java : Case insensitive search in Elasticsearch
- Search text in elastic search ignoring uppercase and lowercase alphabet
- Case insensitive search in Elastic Search