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

**URL:** <https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411>\
**Category:** Discussions en français\
**Created:** [July 19, 2019, 2:29pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411 "2019-07-19T14:29:36Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![morihend](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@morihend](https://discuss.elastic.co/u/morihend)\
**Post date:** [July 19, 2019, 2:29pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411/1 "2019-07-19T14:29:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 19, 2019, 3:05pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411/2 "2019-07-19T15:05:52Z")

</div>

Please [read this](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-query.html#find-docs-null-values)

---

<div class="post-metadata">

**Author:** ![morihend](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@morihend](https://discuss.elastic.co/u/morihend)\
**Post date:** [July 25, 2019, 2:51pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411/3 "2019-07-25T14:51:53Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![gabriel\_tessier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gabriel_tessier/32/27911_2.png) [@gabriel\_tessier](https://discuss.elastic.co/u/gabriel_tessier)\
**Post date:** [July 25, 2019, 11:49pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411/4 "2019-07-25T23:49:32Z")

</div>

Bonjour @morihend

> [@morihend](#):
>
> mais il me parait impossible de changer mon should

impossible n'est pas français:

> **[Impossible n'est pas français](https://fr.wikipedia.org/wiki/Impossible_n%27est_pas_fran%C3%A7ais)**
>
> Ne doit pas être confondu avec Impossible n'est pas anglais ou Impossible... pas français.
> « Impossible n'est pas français » est une citation prêtée à Napoléon Ier. Au moins trois témoins directs attestent l'avoir entendu prononcer cette petite phrase.
> En novembre 1808, Napoléon est en route pour prendre Madrid lors de la guerre d'Espagne. Son armée se trouve bloquée au pied de l'étroit col de Somosierra, dans la sierra de Guadarrama, par quatre batteries espagnoles et des tireurs embusqués qui...

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

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 22, 2019, 11:49pm UTC](https://discuss.elastic.co/t/how-can-i-search-a-null-value-in-an-es-query/191411/5 "2019-08-22T23:49:43Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.