ElasticSearch Range query

Question

I have created the index by using the following mapping:

put test1
{
    "mappings": {
        "type1": {
            "properties": {
                "age": {
                    "type": "text",
                    "fields": {
                        "raw": {
                            "type": "keyword",
                            "ignore_above": 32766
                        }
                    }
                }
            }
        }
    }
}

Added following documents into index:

PUT test1/type1/1/_create
{
  "age":50
}

PUT test1/type1/2/_create
{
  "age":100
}

PUT test1/type1/3/_create
{
  "age":150
}

PUT test1/type1/4/_create
{
  "age":200
}

I have used the following range query to fetch result:

GET test1/_search
{
    "query": {
        "range" : {
            "age" : {
                "lte" : 150
            }
        }
    }
}

It is giving me the following response :

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "test1",
        "_type": "type1",
        "_id": "2",
        "_score": 1,
        "_source": {
          "age": 100
        }
      },
      {
        "_index": "test1",
        "_type": "type1",
        "_id": "3",
        "_score": 1,
        "_source": {
          "age": 150
        }
      }
    ]
  }
}

the above response not showing document having age is 50 it is showing only age is 100 and 150. As 50 also less than 200. What is wrong here? Can anyone help me to get a valid result? In my schema age field type text, I don't want to change it.

How can I get a valid result?

What is your ES version? Almost certain that this can be achieved using Scripted queries — รยקคгรђשค
– รยקคгรђשค, Commented Sep 25, 2018 at 10:25
This answer might help: stackoverflow.com/questions/50825828/… — Val
– Val, Commented Sep 25, 2018 at 12:03

banuj · Accepted Answer · 2018-09-25 10:22:53Z

2

Because age field type is text, the range query is using alphabetically order. So the results are correct:

"100"<"150"
"150"="150"
"50">"150"

If you are ingesting only numbers in age field, you should change the age field type to number, or add another inner field as number, just you did with raw inner field.

answered Sep 25, 2018 at 10:22

banuj

3,26030 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Suraj Dalvi Over a year ago

I have created the index with the inner field with number type:

put test1 { 	"mappings": { 		"type1": { 			"properties": { 				"age": { 					"type": "text", 					"fields": { 						"raw": { 							"type": "keyword", 							"ignore_above": 32766 						}, 						"raw1": { 							"type": "integer" 						} 					} 				} 			} 		} 	} }

And then I have added the following document into it: PUT test1/type1/3/_create { "age":"test" } It gives me the following error: "type": "number_format_exception", "reason": "For input string: \"test\" help me into this?

banuj Over a year ago

As I already told you, you can only add another inner_field as number, only if you are ingesting numbers, not text.

Suraj Dalvi Over a year ago

How we can use the above query with the array for example:

Suraj Dalvi Over a year ago

How we can use the above query with the array for example: PUT test1/type1/4/_create { "age":[200,100,500,400] } Please let me know how we can use the above query with the array?

banuj Over a year ago

@SurajDalvi The array is treated in elasticsearch like an ordinary variable. There are no differences between documents: {"age":10} and {"age":[10,100]}. Actually you can ingest those 2 documents and you will see that both are ingested. The only requirement is that all the array values must have the same type (all ints or all strings etc..)

รยקคгรђשค · Accepted Answer · 2018-09-25 11:16:57Z

1

UPDATE: Tested on local system and it is working.

NOTE: Ideally, you would want the mappings to be correct, but if there is no other choice and you are not the person to decide on the mapping then you can still achieve it by following.

For ES version 6.3 onwards, try this.

 GET test1/type1/_search
    {
        "query": {
            "bool" : {
                "must" : {
                    "script" : {
                        "script" : {
                            "source": "Integer.parseInt(doc['age.raw'].value) <= 150",
                            "lang": "painless"
                         }
                    }
                }
            }
        }
    }

Sources to refer:

https://www.elastic.co/guide/en/elasticsearch/reference/6.3/query-dsl-script-query.html

https://discuss.elastic.co/t/painscript-script-cast-string-as-int/97034

edited Sep 25, 2018 at 11:16

answered Sep 25, 2018 at 10:29

รยקคгรђשค

1,9791 gold badge12 silver badges18 bronze badges

12 Comments

รยקคгรђשค Over a year ago

Please confirm if it works or tell what went wrong. I will change my answer accordingly.

Suraj Dalvi Over a year ago

thanks for the response I have tried above query but getting following error: "type": "illegal_argument_exception", "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [age] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."

รยקคгรђשค Over a year ago

Try now. It might be because the query used text instead of keyword.

รยקคгรђשค Over a year ago

So I made change by using doc['age.raw'] instead of doc['age'].

รยקคгรђשค Over a year ago

@SurajDalvi Hi did you get a chance to try this solution? If it works for you please accept the solution or provide details for what went wrong.

|

nitzien · Accepted Answer · 2018-09-25 10:23:22Z

0

Type for your field age in mapping is set to text. That is reason it is doing dictionary sorting where 50 > 150. Please use long data type. https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html

answered Sep 25, 2018 at 10:23

nitzien

1,29710 silver badges24 bronze badges

Collectives™ on Stack Overflow

ElasticSearch Range query

3 Answers 3

5 Comments

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related