QueryString to search String with colon

Question

i am trying to achieve below condition

orgId = "z2store" and type = "web" and dateTime = "12:17:08"

below query i have written

GET /sample/_search
{
  "bool" : {
    "must" : [
      {
        "term" : {
          "orgId" : {
            "value" : "z2store",
            "boost" : 1.0
          }
        }
      },
      {
        "term" : {
          "type" : {
            "value" : "web",
            "boost" : 1.0
          }
        }
      },
      {
        "query_string" : {
          "query" : "12:17:08",
          "default_field" : "dateTime",
          "fields" : [ ],
          "type" : "best_fields",
          "default_operator" : "or",
          "max_determinized_states" : 10000,
          "enable_position_increments" : true,
          "fuzziness" : "AUTO",
          "fuzzy_prefix_length" : 0,
          "fuzzy_max_expansions" : 50,
          "phrase_slop" : 0,
          "escape" : false,
          "auto_generate_synonyms_phrase_query" : true,
          "fuzzy_transpositions" : true,
          "boost" : 1.0
        }
      }
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
}

below is my java code

BoolQueryBuilder boolQuery = new BoolQueryBuilder().must(QueryBuilders.termQuery("orgId", orgId))
                        .must(QueryBuilders.termQuery("type", "web"));
QueryStringQueryBuilder builder = new QueryStringQueryBuilder("12:17:08");
                        builder.defaultField("dateTime").queryString();
                        boolQuery.must(builder);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder().query(builder)
                        .from((batchNumber - 1) * batchSize).size(batchSize)
                        .sort("@timestamp", SortOrder.DESC);

Above query is not working. Any help will be appreciated. I am using elasticSearch 7.4.

score 1 · Accepted Answer · answered Apr 03 '20 at 09:52

1

You can create your dateTime field with type as date and giving format as hour_minute_second(which takes format as HH:mm:ss) . You can read more about different date formats here https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html.

Below is the mapping of dateTime field:

{
    "mappings": {
        "properties": {
            "dateTime": {
                "type" : "date",
                "format" : "hour_minute_second"
            }
        }
    }
}

Now when you search data with below search query :

{
    "query" : {
        "bool" : {
    "must" : [
      {
        "term" : {
          "orgId" : {
            "value" : "z2store",
            "boost" : 1.0
          }
        }
      },
      {
        "term" : {
          "type" : {
            "value" : "web",
            "boost" : 1.0
          }
        }
      },
      {
       "term" :{
        "dateTime":"12:17:08"
       }
      }
    ],
    "adjust_pure_negative" : true,
    "boost" : 1.0
  }
    }

}

You will get your required result :

"hits": [
            {
                "_index": "datetimeindexf",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.5753641,
                "_source": {
                    "dateTime": "12:17:08",
                    "orgId": "z2store",
                    "type": "web"
                }
            }
        ]

answered Apr 03 '20 at 09:52

applied below setting to existing index (sample) PUT /sample/_mapping { "properties": { "dateTime": { "type" : "date", "format" : "hour_minute_second" } } } getting parsing exception. – TeamZ Apr 03 '20 at 10:19
1

You can’t change the mapping or field type of an existing field. Changing an existing field could invalidate data that’s already indexed.If you need to change the mapping of a field, create a new index with the correct mapping and reindex your data into that index. You can refer the same regarding updating mapping of index https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-put-mapping.html – Apr 03 '20 at 10:24
If you dont want to create new index you can create a new field in existing mapping with this new type – Apr 03 '20 at 10:28
i added a new field to existing mapping PUT /sample/_mapping { "properties": { "dateTimestamp": { "type": "date", "format": "hour_minute_second" } } } – TeamZ Apr 03 '20 at 10:44
It worked fine with me . I have updated my indenx by calling PUT api "http://localhost:9200/sample/_mapping" with body as { "properties": { "dateTimestamp": { "type": "date", "format":"hour_minute_second" } } } It returned response : { "acknowledged": true } Are you still getting exception ? – Apr 03 '20 at 10:51
result is coming empty – TeamZ Apr 03 '20 at 10:58
1

Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210863/discussion-between-prerna-gupta-and-teamz). – Apr 03 '20 at 10:59

QueryString to search String with colon

1 Answers1