Create Elasticsearch curl query for not null and not empty("")

Question

How can i create Elasticsearch curl query to get the field value which are not null and not empty(""),

Here is the mysql query:

select field1 from mytable where field1!=null and field1!="";

Answer 1

A null value and an empty string both result in no value being indexed, in which case you can use the exists filter

curl -XGET 'http://127.0.0.1:9200/test/test/_search?pretty=1'  -d '
{
   "query" : {
      "constant_score" : {
         "filter" : {
            "exists" : {
               "field" : "myfield"
            }
         }
      }
   }
}
'

Or in combination with (eg) a full text search on the title field:

curl -XGET 'http://127.0.0.1:9200/test/test/_search?pretty=1'  -d '
{
   "query" : {
      "filtered" : {
         "filter" : {
            "exists" : {
               "field" : "myfield"
            }
         },
         "query" : {
            "match" : {
               "title" : "search keywords"
            }
         }
      }
   }
}
'

Answer 2

As @luqmaan pointed out in the comments, the documentation says that the filter exists doesn't filter out empty strings as they are considered non-null values.

So adding to @DrTech's answer, to effectively filter null and empty string values out, you should use something like this:

{
    "query" : {
        "constant_score" : {
            "filter" : {
                "bool": {
                    "must": {"exists": {"field": "<your_field_name_here>"}},
                    "must_not": {"term": {"<your_field_name_here>": ""}}
                }
            }
        }
    }
}

Answer 3

On elasticsearch 5.6, I have to use command below to filter out empty string:

GET /_search
{
    "query" : {
        "regexp":{
            "<your_field_name_here>": ".+"
        }
    }
}  

Answer 4

Wrap a Missing Filter in the Must-Not section of a Bool Filter. It will only return documents where the field exists, and if you set the "null_value" property to true, values that are explicitly not null.

{
  "query":{
     "filtered":{
        "query":{
           "match_all":{}
        },
        "filter":{
            "bool":{
              "must":{},
              "should":{},
              "must_not":{
                 "missing":{
                    "field":"field1",
                    "existence":true,
                    "null_value":true
                 }
              }
           }
        }
     }
  }
}