CODEWITHSUNDEEP
elasticsearch
uttam palkar
uttam palkar

Reputation: 1655

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

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

Upvotes: 117

Views: 207153

Answers (13)

Ibtesam Latif
Ibtesam Latif

Reputation: 698

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

Upvotes: 0

cavpollo
cavpollo

Reputation: 4318

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

Upvotes: 52

bigstone1998
bigstone1998

Reputation: 546

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

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

Upvotes: 41

Arthur_Morgan
Arthur_Morgan

Reputation: 73

ES 7.x

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

Upvotes: 2

Eric Tan
Eric Tan

Reputation: 1463

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

Upvotes: 6

Dean Jain
Dean Jain

Reputation: 2198

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

Upvotes: 2

Ashfaq Hussain
Ashfaq Hussain

Reputation: 73

Elastic search Get all record where condition not empty.

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

Upvotes: 0

LaserJesus
LaserJesus

Reputation: 8540

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.

Upvotes: 8

Luka Lopusina
Luka Lopusina

Reputation: 2647

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.

Upvotes: 9

Zach
Zach

Reputation: 9721

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

Upvotes: 32

Sanshila
Sanshila

Reputation: 158

You can use not filter on top of missing.

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

Upvotes: 6

James-Jesse Drinkard
James-Jesse Drinkard

Reputation: 15703

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

Upvotes: 1

DrTech
DrTech

Reputation: 17319

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

Upvotes: 104

Related Questions