Search docs with (term = "val" OR not_exists)

Elastic Stack Elasticsearch
1

I have a _search query running that checks certain properties and aggregates values based on the search. One of the search criteria is:
{ "term": { "Status": 0} }

"Status" was a property added after documents were already indexed. By default, I treat Status = 0. So I'm trying to search for documents where Status = 0 OR the property doesn't exist in which case I want to treat as 0 anyways.

Is there a way to structure my search query to handle this? Or possibly set a default value when adding a new property to a mapping?

2

I was able to accomplish this by using a "script" like so:

{ "script": { "script": { "source": "doc['Status'].empty || doc['Status'].value == 0" } } }

3

This is not search, this is full index scan. If you want to implement it as a search you can combine together your term query with exists query, using bool query.

4

@Igor_Motov Hmmm...ok. Can you elaborate a little on that? I couldn't figure out how to have an OR using the bool query. Here is the full example of what I'm trying to accomplish. I want to find all documents with a CloseDate = '2018-02-01' and Status = 0. But documents that were indexed prior to adding Status don't have that property. So currently it's filtering those documents out

"query": {
		"bool": {
			"must": [{
				"term": { "CloseDate": "2018-02-01" }
			}, {
				"term": { "Status": 0 }
			}]
		}
	},
	"aggs": {
		"GrossSales": {
			"sum": { "field": "GrossSales" }
		}
	}
}
5

This is how I was using the script

"query": {
		"bool": {
			"must": [{
				"term": { "CloseDate": "2018-02-01" }
			}, {
				"script": {
					"script": {
						"source": "(doc['Status'].empty || doc['Status'].value == 0)"
					}
				}
			}]
		}
	},
	"aggs": {
		"GrossSales": {
			"sum": { "field": "GrossSales" }
		}
	}
}
6 
PUT test/doc/1
{
  "CloseDate": "2018-02-01",
  "Status": 0,
  "GrossSales": 100
}

PUT test/doc/2
{
  "CloseDate": "2018-02-02",
  "Status": 0,
  "GrossSales": 200
}

PUT test/doc/3
{
  "CloseDate": "2018-02-01",
  "GrossSales": 200
}

PUT test/doc/4
{
  "CloseDate": "2018-02-01",
  "Status": 1,
  "GrossSales": 400
}

POST test/doc/_search
{
  "query": {
    "bool": {
      "must": [ { "term": { "CloseDate": "2018-02-01" } } ],
      "should": [
        { "term": { "Status": 0 } },
        {
          "bool": {
            "must_not": [ { "exists": { "field": "Status" } } ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  },
  "aggs": {
    "GrossSales": {
      "sum": {
        "field": "GrossSales"
      }
    }
  }
}
7

@Igor_Motov Thank you!!! That makes perfect sense

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.