ElasticSearch: How to write a query in which a string field is either empty or empty?

Question

I want to check documents with media_url == '' || media_url == null media_url == '' || media_url == null . I have a request:

 { "engagements": [ "blah" ], "query": { "from": 0, "size": 2, "sort": [ { "bookmarked": { "order": "desc" } }, { "created_at": { "order": "desc" } } ], "facets": {}, "query": { "filtered": { "query": { "match_all": {} }, "filter": { "bool": { "must": [ { "term": { "car_id": "78778" } }, { "range": { "created_at": { "gte": "2015-04-12T04:00:00.000Z", "lte": "2015-05-13T03:59:59.999Z" } } }, { "term": { "media_url": "" } } ], "should": [ { "term": { "bookmarked": false } } ] } } } }, "aggregations": { "word_frequencies": { "terms": { "field": "text", "size": 150 } } }, "highlight": { "fields": { "text": { "fragment_size": 1500 } } } }, "api": "_search" } 

However, if I do what I do above, then entries that are null will not be returned. What should I do to return records with "either" or "zero" as the value of media_url?

Answer 1

Perhaps you can try using the "or" filter. http://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-or-filter.html

 { "or": [ { "term": { "media_url": "" } }, { "term": { "media_url": null } } ] } 

Edit: here is the full request (unverified, as I don't have an example document / index template)

 { "engagements": [ "blah" ], "query": { "from": 0, "size": 2, "sort": [ { "bookmarked": { "order": "desc" } }, { "created_at": { "order": "desc" } } ], "facets": {}, "query": { "filtered": { "query": { "match_all": {} }, "filter": { "bool": { "must": [ { "term": { "car_id": "78778" } }, { "range": { "created_at": { "gte": "2015-04-12T04:00:00.000Z", "lte": "2015-05-13T03:59:59.999Z" } } }, { "or": [ { "term": { "media_url": "" } }, { "term": { "media_url": null } } ] } ], "should": [ { "term": { "bookmarked": false } } ] } } } }, "aggregations": { "word_frequencies": { "terms": { "field": "text", "size": 150 } } }, "highlight": { "fields": { "text": { "fragment_size": 1500 } } } }, "api": "_search" } 

Answer 2

You can use the missing filter to eliminate the null value or the field itself. You can combine them with an empty string term to achieve what you want.

 { "or": [ { "term": { "media_url": "" } }, { "missing": { "field": "media_url" } } ] } 

Use the above instead of the single-terminal query for "media_url" in the required sentence of your boolean filter.