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!="";

all Elastic questions should have the version of Elastic you're using. It's mandatory because even the minor versions have so many changes — Henley, Jan 19 '16 at 15:25

score 100 · Answer 1 · answered Feb 07 '13 at 10:23

100

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"
            }
         }
      }
   }
}
'

answered Feb 07 '13 at 10:23

DrTech

17,031
5
54
48

hi, just want to know how can i do for mutiple field. :) – uttam palkar Feb 07 '13 at 11:28
1

just wrap multiple `exists` filters in an `and` filter http://www.elasticsearch.org/guide/reference/query-dsl/and-filter.html – DrTech Feb 07 '13 at 16:27
There's no need to wrap the filter with a query. – WhatIsHeDoing Dec 03 '14 at 12:14
43

This doesn't filter out empty strings. `An empty string is a non-null value.` - http://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-filter.html – Luqmaan Apr 01 '15 at 17:22
1

note the convenient single line format curl -XGET 'http://127.0.0.1:9200/test/test/_search?q=_exists_:field1' – Rene Wooller Sep 09 '15 at 10:12
"filtered" has been deprecated, use the boolean query: https://stackoverflow.com/a/65821508/1172996 – Raymond Holmboe Jul 28 '23 at 00:19

score 48 · Answer 2 · edited Oct 07 '22 at 06:53

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>": ""}}
                }
            }
        }
    }
}

score 38 · Answer 3 · edited Oct 07 '22 at 06:50

38

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

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

edited Oct 07 '22 at 06:50

Jaroslav Bezděk

6,967
6
29
46

answered Jan 22 '18 at 15:34

bigstone1998

516
4
4

3

sadly yes, that is the only solution i found too... very strange there is no simple query to say field = '' ... – danvasiloiu Jan 31 '18 at 10:56
3

regex is not the best in terms of performance. check https://stackoverflow.com/questions/25561981/find-documents-with-empty-string-value-on-elasticsearch – danvasiloiu Jan 31 '18 at 15:59
1

This is the only solution that works on fields that don't have "fielddata" enabled, thanks – Ron Serruya May 19 '20 at 07:28

score 32 · Answer 4 · edited Jun 28 '16 at 13:40

32

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
                 }
              }
           }
        }
     }
  }
}

edited Jun 28 '16 at 13:40

czerasz

13,682
9
53
63

answered Feb 07 '13 at 10:10

Zach

9,591
1
38
33

2

Hurr...forgot about the Exists filter. Use DrTech's solution, mine is a less elegant way to do the same. – Zach Feb 07 '13 at 16:43
Huzzah! You just ended about four hours of misery. Thanks :) – simonmorley Jun 14 '13 at 01:03
this approach worked for me in v1.0.3, but doesn't work any more in v1.5 – phirschybar Apr 29 '15 at 12:13
@Zach Do you think you could help me with http://stackoverflow.com/questions/33630013/ignoring-records-marked-as-deleted-in-elasticsearch by any chance? – BentCoder Nov 13 '15 at 12:29
1

this was very helpful and worked well as a stopgap, because exits isn't in the version of elasticsearch we were using. Thanks @zach – rubyisbeautiful Jan 19 '16 at 12:47

score 9 · Answer 5 · answered Jun 25 '18 at 09:42

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.

LaserJesus · Answer 6 · 2018-12-06T23:13:44.983

8

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.

edited Dec 06 '18 at 23:13

answered Dec 06 '18 at 23:00

LaserJesus

8,230
7
47
65

This doesn't work for `type=text` fields. But can be fixed by extending the ES schema `"myfield": { "type": "text", fielddata: true, "fields": { "keyword": { "type": "keyword" } } }` – James McGuigan Jun 14 '20 at 14:20
Why do we have to use the `.keyword`? Does it have anything to do with the analyzed field since `must_not` is exact match? If so, if we don't use `.keyword` what does that imply? Btw, using `.keyword` works for me. – Luyang Du Oct 13 '20 at 21:31
1

@LuyangDu The schema defines two fields, myfield and myfield.keyword. The "myfield.keyword" field is an explicit "keyword" field which holds a maximum of 256 characters and is used for exact matching only, compared to "myfield" which is an open text field which can have tokenizers and other processing applied. These two fields therefore work very differently and only the keyword field will represent both null and empty strings as an empty string, allowing you to negate against that field being empty. – LaserJesus Oct 15 '20 at 16:18

score 6 · Answer 7 · edited Feb 22 '16 at 13:05

6

You can use not filter on top of missing.

"query": {
  "filtered": {
     "query": {
        "match_all": {}
     },
     "filter": {
        "not": {
           "filter": {
              "missing": {
                 "field": "searchField"
              }
           }
        }
     }
  }
}

edited Feb 22 '16 at 13:05

yAnTar

4,269
9
47
73

answered Apr 10 '15 at 11:49

Sanshila

158
1
6

I was able to get a missing query working, but I kept using "exists" on an array to see if I could find a "not null" type value and it wasn't working. This was the correct solution for me. Thanks! – James Drinkard Jan 19 '16 at 17:02

score 6 · Answer 8 · answered Nov 08 '20 at 07:29

Here's the query example to check the existence of multiple fields:

{
  "query": {
    "bool": {
      "filter": [
        {
          "exists": {
            "field": "field_1"
          }
        },
        {
          "exists": {
            "field": "field_2"
          }
        },
        {
          "exists": {
            "field": "field_n"
          }
        }
      ]
    }
  }
}

score 2 · Answer 9 · edited Jun 14 '20 at 14:22

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"
  }

score 2 · Answer 10 · answered Jan 12 '22 at 08:10

2

ES 7.x

{
  "_source": "field", 
  "query": {
    "bool": {
      "must": [
        {
          "exists": {
            "field":"field"
          }
        }
      ],
      "must_not": [
        {
          "term": {
            "field.keyword": {
              "value": ""
            }
          }
        }
      ]
    }
  }
}

answered Jan 12 '22 at 08:10

Arthur_Morgan

73
7

4

"field": "" – mutas Apr 14 '22 at 09:18

James Drinkard · Answer 11 · 2016-02-03T04:12:18.093

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": ""
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

score 0 · Answer 12 · answered Apr 24 '19 at 10:32

0

Elastic search Get all record where condition not empty.

const searchQuery = {
      body: {
        query: {
          query_string: {
            default_field: '*.*',
            query: 'feildName: ?*',
          },
        },
      },
      index: 'IndexName'
    };

answered Apr 24 '19 at 10:32

Ashfaq Hussain

73
1
4

2

Please add some description about your answer. Please refer https://stackoverflow.com/help/how-to-answer – Angel F Syrus Apr 24 '19 at 10:58

score 0 · Answer 13 · edited Jun 28 '23 at 13:28

0

You need to use bool query with must/must_not and exists.

To get where place is null:

{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "place"
        }
      }
    }
  }
}

To get where place is not null:

{
  "query": {
    "bool": {
      "must": {
        "exists": {
          "field": "place"
        }
      }
    }
  }
}

edited Jun 28 '23 at 13:28

bfontaine

18,169
13
73
107

answered Sep 29 '20 at 13:11

Ibtesam Latif

1,175
1
10
13

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

13 Answers13

Linked