How can i search a null value in an ES query?

[morihend]

Hello,

I have a query ES that must search a null value in a field that is a date type in my BDD.
Logstash is my program that put datas in the ES.

Look at my query below. The problem is here : "DATE_FIN": null

{
"query": {
"bool": {
"must": [
{
"terms": {
"TYPE_DOCUMENT": [
"contrat",
"avenant"
]
}
},
{
"match": {
"STATUT": "ACTIF"
}
},
{
"match": {
"enreg": "1236259"
}
}
],
"should": [
{
"range": {
"DATE_FIN": {
"gte": "2019-07-19"
}
}
},
{
"term": {
"DATE_FIN": null
}
},
{
"range": {
"DATE_FIN": {
"lte": "0001-01-01"
}
}
}
],
"minimum_should_match": 1
}
}
}

The ES response is this one:

{
"error": {
"root_cause": [
{
"type": "illegal_argument_exception",
"reason": "field name is null or empty"
}
],
"type": "illegal_argument_exception",
"reason": "field name is null or empty"
},
"status": 400
}.

How can I do ?

Thanks in advance for your response

[dadoonet (David Pilato)]

Please read this about how to format.

You can try a must_not bool clause with an exists query. See https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-query.html#find-docs-null-values

[morihend]

Bonjour,

Je me permet d'écrire en français car on a l'air d’être des compatriotes.
Merci pour votre retour mais il me parait impossible de changer mon should en must_not pour tester si mon champ DATE_FIN est null.

Revoici mon code :

{
                "query": {
                       "bool": {
                             "must": [
                                   {
                                        "terms": 
    {"TYPE_DOCUMENT": [ "contrat","avenant"]}
                                   },
                                  
                                  {
                                          "match": { "STATUT": "ACTIF"}
                                   },
                
                                  {
                                           "match": {"enreg": "1236259"}
                                   }
                          ],
                
                         "should": [
                                 {
                                     "range": { "DATE_FIN": { "gte": "2019-07-19"} }
                                 },
                        
                               {   
                                     "term": { "DATE_FIN": null}
                               },
                
                              {    
                                     "range": { "DATE_FIN": { "lte": "0001-01-01" }}
                               }
                         ],
                           "minimum_should_match": 1
                      }
           }
    }

Avez vous une autre idée ?

Merci d'avance,

[gabriel_tessier]

Bonjour @morihend

@morihend

mais il me parait impossible de changer mon should

impossible n'est pas français:

Impossible n'est pas français

Est-ce que cette combinaison retourne le résultat souhaité ?

"should": [
                                 {
                                     "range": { "DATE_FIN": { "gte": "2019-07-19"} }
                                 },
                        
                               {"bool":{"must_not":[{   
                                     "exists": { "field": "DATE_FIN"}
                               }]}},
                
                              {    
                                     "range": { "DATE_FIN": { "lte": "0001-01-01" }}
                               }
                         ]