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

User · Answer

You can use a bool combination query with must/must_not which gives great performance and returns all records where the field is not null and not empty.

bool must_not is like "NOT AND" which means field!="", bool must exist means its !=null.

so effectively enabling: where field1!=null and field1!=""

GET  IndexName/IndexType/_search
{
    "query": {
        "bool": {
            "must": [{
                "bool": {
                    "must_not": [{
                        "term": { "YourFieldName": ""}
                    }]
                }
            }, {
                "bool": {
                    "must": [{
                      "exists" : { "field" : "YourFieldName" }
                    }]
                }
            }]
        }   
    }
}

ElasticSearch Version:

  "version": {
    "number": "5.6.10",
    "lucene_version": "6.6.1"
  }

User · Answer

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

User · Answer

You can do that with bool query and combination of must and must not like this   GET index  search        query              bool                  must                       exists     field    field1                                must not                       term     field1                                          I tested this with Elasticsearch 5 6 5 in Kibana

User · Answer

The only solution here that worked for me in 5 6 5 was bigstone1998 s regex answer  I d prefer not to use a regex search though for performance reasons  I believe the reason the other solutions don t work is because a standard field will be analyzed and as a result have no empty string token to negate against  The exists query won t help on it s own either since an empty string is considered non-null   If you can t change the index the regex approach may be your only option  but if you can change the index then adding a keyword subfield will solve the problem   In the mappings for the index    myfield          type    text        fields              keyword                  ignore above   256               type    keyword                      Then you can simply use the query        query          bool            must              exists                field    myfield                            must not              term                myfield keyword                                      Note the  keyword in the must not component

User · Answer

Elastic search Get all record where condition not empty   const searchQuery           body            query              query string                default field                     query   feildName                                              index   IndexName

User · Answer

Here s the query example to check the existence of multiple fields       quot query quot          quot bool quot            quot filter quot                          quot exists quot                  quot field quot    quot field 1 quot                                              quot exists quot                  quot field quot    quot field 2 quot                                              quot exists quot                  quot field quot    quot field n quot

User · Answer

You need to use bool query with must must not and exists To get where place is null      quot query quot          quot bool quot            quot must not quot              quot exists quot                quot field quot    quot place quot                                 To get where place is not null      quot query quot          quot bool quot            quot must quot              quot exists quot                quot field quot    quot place quot

User · Answer

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     lt your field name here gt                           must not     term      lt your field name here gt

User · Answer

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

User · Answer

You can use not filter on top of missing    query        filtered           query              match all                    filter              not                 filter                    missing                       field    searchField

User · Answer

On elasticsearch 5 6  I have to use command below to filter out empty string       GET   search                query                   regexp                      lt your field name here gt

User · Answer

We are using Elasticsearch version 1 6 and I used this query from a co-worker to cover not null and not empty for a field          query          filtered            query              match all                      filter              bool                must                                  exists                      field    myfieldName                                                              not                      filter                        term                          myfieldName

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

Examples related to elasticsearch