ElasticSearch: How to write query where string field is either null or empty?

Question

I want to check for documents that have media_url == '' || media_url == null. I have a query:

{
    "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 records that are set to null wouldn't be returned. What should I do to return records with either '' or null as their media_url value?

phuc77 · Accepted Answer · 2015-05-13 23:57:27Z

7

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's the full query (untested since 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"
}

edited May 13, 2015 at 23:57

answered May 13, 2015 at 20:59

phuc77

6,9453 gold badges17 silver badges11 bronze badges

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

3 Comments

bigpotato Over a year ago

Hi where exactly would I place this in my query string??

phuc77 Over a year ago

Updated my reply, let me know how it goes or update your question with example document and mapping.

bigpotato Over a year ago

The correct answer was using missing: { "field": "media_url" } instead of "media_url": null, but still going to accept your answer since it's almost correct! Thanks!!

Prabin Meitei · Accepted Answer · 2015-05-14 03:36:45Z

5

You can use the missing filter to take care of null value or field itself is missing. You can combine the same 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 term query for "media_url" in the must clause of your Boolean filter.

answered May 14, 2015 at 3:36

Prabin Meitei

2,0001 gold badge13 silver badges16 bronze badges

3 Comments

doctorlove Over a year ago

The missing filter has, erm, gone missing since 5.something

Prabin Meitei Over a year ago

@doctorlove thanks for pointing out. We can use the exists query under a "must_not" clause for the same effect

regularmike Over a year ago

@PrabinMeitei but how would you use that inside the "or" (which is now "should")? I can't figure out the proper nesting.

Collectives™ on Stack Overflow

ElasticSearch: How to write query where string field is either null or empty?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related