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

Bhavya · Accepted Answer · 2020-07-18 10:06:17Z

5

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

edited Jul 18, 2020 at 10:06

answered Jul 18, 2020 at 9:42

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

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

3 Comments

Bhavya Over a year ago

@Xoshabi did you get a chance to go through my answer, looking forward to get feedback from u

Xoshabi Over a year ago

thank you @Bhavya; I found that my database was empty so it didn't return anything .

Bhavya Over a year ago

@Xoshabi thank u for accepting the answer :) Can u pls upvote this also by clicking on the up arrow on the left side of answer :)

Vit Ias · Accepted Answer · 2020-07-18 11:33:02Z

0

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

answered Jul 18, 2020 at 11:33

Vit Ias

8035 silver badges18 bronze badges

Comments

Rafiq · Accepted Answer · 2022-03-19 13:44:12Z

                    query: {
                      bool: {
                        must: [
                          {
                            bool: {
                              must_not: {
                                missing: {
                                  field: 'follower',
                                  existence: true,
                                  null_value: true,
                                },
                              },
                            },
                          },
                          {
                            nested: {
                              path: 'follower',
                              query: {
                                match: {
                                  'follower.id': req.currentUser?.id,
                                },
                              },
                            },
                          },
                        ],
                      },
                    },

Collectives™ on Stack Overflow

How do you filter on not null values elasticsearch?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related