How do you filter on not null values elasticsearch?

Question

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 elasticsearch-dsl(python)?

I tried things like:

s = Mytable.search().query(
Q('match', field_2 ='alpha')
).filter(~Q('missing', field='field_1'))

but it returns elements with null values of field_1

Also, I tried this down, but it didn't work

field_name_1 = 'field_2'
value_1 = "alpha"
field_name_2 = 'field_1'
value_2 = " "
filter = { 
    "query": {
        "bool": {
        "must": [
            {
            "match": {
                field_name_1 : value_1
            }
            },
            {
            "bool": {
                "should": [
                    {
                    "bool": {
                        "must_not": [
                            {
                                field_name_2: {
                                    "textContent": "*"
                                }
                            }
                        ]
                    } }
                ]
            }
            }
        ]
        }
    }
    }
  

Answer 1

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

SELECT ALL FROM Mytable WHERE field_1 NOT NULL and 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
    }
  }
]