Elasticsearch 6.3.2 terms match empty array "plus" others

Question

In my database, a post can have zero (0) or more categories represented as an array.

When I do the query, to look within those categories, passing some values:

{
  "query": {
    "bool": {
      "should": {
        "terms": {
          "categories": ["First", "Second", "And so on"]
        }
      }
    }
  }
}

And it works well, I have the records I'm expecting. But the problem comes when I want to include those posts, where categories is an empty array ([]).

I'm upgrading from an old version of ES (1.4.5) now to the version 6.3.2, and this code was made using "missing", which has been deprecated.

I've tried changing the mapping adding the famouse "null_value": "NULL", and query then, but didn't work. Also tried the combination of should with must_not, as suggested for upgrading "missing", but didn't work.

How can I achieve this? Meaning that if I have indexed:

Post.new(id: 1, title: '1st', categories: [])
Post.new(id: 2, title: '2nd', categories: ['news', 'tv'])
Post.new(id: 3, title: '3rd', categories: ['tv', 'trending'])
Post.new(id: 4, title: '4th', categories: ['movies'])
Post.new(id: 5, title: '5th', categories: ['technology', 'music'])

The result should return posts number 1, 2 y 3 - the ones that have "news", "tv" or an empty array as categories.

Answer 1

Missing can be replicated using exists inside must_not. You have to modify query as below:

{
  "query": {
    "bool": {
      "should": [
        {
          "terms": {
            "categories": [
              "First",
              "Second",
              "And so on"
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "categories"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

You can read about it here.