How Do You Filter On Not Null Values Elasticsearch?

I am trying to filter out values with not null : Exemple with sql SELECT ALL FROM Mytable WHERE field_1 NOT NULL and field_2 ='alpha' How should I be writing this query in elastic

Solution 1:

I am not familiar with elasticsearch-dsl(python), but the following search query, will get you the same search result as you want :

SELECTALLFROM Mytable WHERE field_1 NOTNULLand field_2 ="alpha"

With the help of below search query, the search result will be such that name="alpha" AND cost field will not be null. You can refer exists query to know more about this.

Index Data:

{"name":"alpha","item":null}{"name":"beta","item":null}{"name":"alpha","item":1}{"name":"alpha","item":[]}

Search query:

You can combine a bool query with a exists query like this:

{"query":{"bool":{"must":[{"term":{"name":"alpha"}},{"exists":{"field":"item"}}]}}}

Search Result:

"hits":[{"_index":"my-index","_type":"_doc","_id":"4","_score":1.6931472,"_source":{"name":"alpha","item":1}}]

Solution 2:

You can try this one:

s = Mytable.search()
.query(Q('bool', must=[Q('match', field_2='alpha'), Q('exists', field='field_1')]))

This is the way to use boolean compound query