Finding ElasticSearch records matching empty and null values

Question

I have some elasticsearch records that are being stored as either an empty string, or a null value. I am trying to develop a query that will allow me to return these from the index. I came up with:

{
    'query': {
        'filtered': {
            'filter': {
                'bool': {
                    'should': [
                        {'term': {'field1': ''}},
                        {"missing" : {"field": "field1"}},
                    ],
                }
            }
        }
    }
}

Which works as intended for my purpose, and returns the correct row. However, if I try and search for any more than a single field, the 'should' clause OR's the two fields together. This is a problem, because I want there to be an AND relationship:

{
    'query': {
        'filtered': {
            'filter': {
                'bool': {
                    'should': [
                        {'term': {'field1': ''}},
                        {"missing" : {"field": "field1"}},
                        # these fields should be AND but are OR
                        {'term': {'field2': ''}},
                        {"missing" : {"field": "field2"}},
                    ],
                }
            }
        }
    }
}

Is there anyway I can do the above with a single filter, or AND the two filters together?

Answer 1

You could use the and filter for that purpose, and AND the two bool/should filters, like this:

{
  "query": {
    "filtered": {
      "filter": {
        "and": [
          {
            "bool": {
              "should": [
                {
                  "term": {
                    "field1": ""
                  }
                },
                {
                  "missing": {
                    "field": "field1"
                  }
                }
              ]
            }
          },
          {
            "bool": {
              "should": [
                {
                  "term": {
                    "field2": ""
                  }
                },
                {
                  "missing": {
                    "field": "field2"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}

Or you can also bool/must two or filters like this:

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "or": [
                {
                  "term": {
                    "field1": ""
                  }
                },
                {
                  "missing": {
                    "field": "field1"
                  }
                }
              ]
            },
            {
              "or": [
                {
                  "term": {
                    "field2": ""
                  }
                },
                {
                  "missing": {
                    "field": "field2"
                  }
                }
              ]
            }
          ]
        }
      }
    }
  }
}