How to return all documents where a field does not exist, but if it exists return documents with value “tag1”

I am facing a problem in building an elasticsearch query in Java.

I want to search all the documents where either a field X is not present OR if it is present, return all those documents where the value inside field X is “tag1”

Currently, I have written this query in Java:

BoolQueryBuilder boolQueryBuilder = boolQuery();`
boolQueryBuilder.mustNot(QueryBuilders.existsQuery("X"));`
boolQueryBuilder.filter(termsQuery("X", "tag1"));`

The SQL query for this would be something like:

select * from table_name where X is null or x = 'tag1';

But doing this gives me empty search results. However, if I search each of these queries individually, it works fine.

Answer

You need to use should

BoolQueryBuilder subBoolQuery = boolQuery();
subBoolQuery.mustNot(QueryBuilders.existsQuery("X"));

BoolQueryBuilder boolQueryBuilder = boolQuery();
BoolQueryBuilder.minimumShouldMatch(1);
boolQueryBuilder.should(subBoolQuery);
boolQueryBuilder.should(termsQuery("X", "tag1"));

Elasticsearch query :

{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "X": {
              "value": "tag1"
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "X"
                }
              }
            ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}