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 Wing Chiu
– Henley Wing Chiu, Commented Jan 19, 2016 at 15:25

DrTech · Accepted Answer · 2013-02-07 10:23:47Z

105

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 7, 2013 at 10:23

DrTech

17.4k5 gold badges56 silver badges48 bronze badges

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

6 Comments

uttam palkar Over a year ago

hi, just want to know how can i do for mutiple field. :)

DrTech Over a year ago

just wrap multiple exists filters in an and filter elasticsearch.org/guide/reference/query-dsl/and-filter.html

WhatIsHeDoing Over a year ago

There's no need to wrap the filter with a query.

Luqmaan Over a year ago

This doesn't filter out empty strings. An empty string is a non-null value. - elastic.co/guide/en/elasticsearch/reference/current/…

Rene Wooller Over a year ago

note the convenient single line format curl -XGET '127.0.0.1:9200/test/test/_search?q=_exists_:field1'

|

Jaroslav Bezděk · Accepted Answer · 2022-10-07 06:53:39Z

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

edited Oct 7, 2022 at 6:53

Jaroslav Bezděk

7,7156 gold badges34 silver badges59 bronze badges

answered Jul 30, 2015 at 18:55

cavpollo

4,3386 gold badges44 silver badges71 bronze badges

Comments

Jaroslav Bezděk · Accepted Answer · 2022-10-07 06:50:00Z

41

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 7, 2022 at 6:50

Jaroslav Bezděk

7,7156 gold badges34 silver badges59 bronze badges

answered Jan 22, 2018 at 15:34

bigstone1998

5464 silver badges4 bronze badges

3 Comments

danvasiloiu Over a year ago

sadly yes, that is the only solution i found too... very strange there is no simple query to say field = '' ...

danvasiloiu Over a year ago

regex is not the best in terms of performance. check stackoverflow.com/questions/25561981/…

Ron Serruya Over a year ago

This is the only solution that works on fields that don't have "fielddata" enabled, thanks

czerasz · Accepted Answer · 2016-06-28 13:40:32Z

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, 2016 at 13:40

czerasz

14.4k10 gold badges56 silver badges63 bronze badges

answered Feb 7, 2013 at 10:10

Zach

9,7511 gold badge42 silver badges33 bronze badges

5 Comments

Zach Over a year ago

Hurr...forgot about the Exists filter. Use DrTech's solution, mine is a less elegant way to do the same.

simonmorley Over a year ago

Huzzah! You just ended about four hours of misery. Thanks :)

phirschybar Over a year ago

this approach worked for me in v1.0.3, but doesn't work any more in v1.5

BentCoder Over a year ago

@Zach Do you think you could help me with stackoverflow.com/questions/33630013/… by any chance?

rubyisbeautiful Over a year ago

this was very helpful and worked well as a stopgap, because exits isn't in the version of elasticsearch we were using. Thanks @zach

Luka Lopusina · Accepted Answer · 2018-06-25 09:42:39Z

9

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.

answered Jun 25, 2018 at 9:42

Luka Lopusina

2,6473 gold badges29 silver badges33 bronze badges

Comments

LaserJesus · Accepted Answer · 2018-12-06 23:13:44Z

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 6, 2018 at 23:13

answered Dec 6, 2018 at 23:00

LaserJesus

8,5909 gold badges52 silver badges69 bronze badges

3 Comments

James McGuigan Over a year ago

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

Luyang Du Over a year ago

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.

LaserJesus Over a year ago

@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.

yAnTar · Accepted Answer · 2016-02-22 13:05:28Z

6

You can use not filter on top of missing.

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

edited Feb 22, 2016 at 13:05

yAnTar

4,64010 gold badges51 silver badges74 bronze badges

answered Apr 10, 2015 at 11:49

Sanshila

1581 silver badge6 bronze badges

1 Comment

James-Jesse Drinkard Over a year ago

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!

Eric Tan · Accepted Answer · 2020-11-08 07:29:27Z

6

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

answered Nov 8, 2020 at 7:29

Eric Tan

1,46317 silver badges14 bronze badges

Comments

James McGuigan · Accepted Answer · 2020-06-14 14:22:24Z

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

Arthur_Morgan · Accepted Answer · 2022-01-12 08:10:49Z

2

ES 7.x

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

answered Jan 12, 2022 at 8:10

Arthur_Morgan

738 bronze badges

1 Comment

mutas Over a year ago

"field": "<theactualfieldname>"

James-Jesse Drinkard · Accepted Answer · 2016-02-03 04:12:18Z

1

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

edited Feb 3, 2016 at 4:12

answered Feb 3, 2016 at 3:49

James-Jesse Drinkard

15.8k16 gold badges120 silver badges141 bronze badges

Comments

bfontaine · Accepted Answer · 2023-06-28 13:28:06Z

1

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, 2023 at 13:28

bfontaine

21.3k14 gold badges88 silver badges127 bronze badges

answered Sep 29, 2020 at 13:11

Ibtesam Latif

7081 gold badge10 silver badges13 bronze badges

Comments

Ashfaq Hussain · Accepted Answer · 2019-04-24 10:32:31Z

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, 2019 at 10:32

Ashfaq Hussain

731 silver badge4 bronze badges

1 Comment

Angel F Syrus Over a year ago

Please add some description about your answer. Please refer stackoverflow.com/help/how-to-answer

Collectives™ on Stack Overflow

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

13 Answers 13

6 Comments

Comments

3 Comments

5 Comments

Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

13 Answers 13

6 Comments

Comments

3 Comments

5 Comments

Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related