Elastic Search filter query for null values-CodePudding

I am trying to write a query where it searches in elastic that if fields is null then filter it out. In my case if both battery temperature and temperature value is null then remove it from the bucket, if one value is null then no need to filter.

Above query is used and in some cases both temperature and battery values are coming null which I need to remove from the buckets.

GET ts-history*/_search/?pretty
{
  "aggs": {
    "timestamp": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "1s",
        "time_zone": "America/New_York",
        "min_doc_count": 1
      },
      "aggs": {
        "temperature75cea6bd-21bb-4794-8314-0291a7c9ad71": {
          "avg": {
            "field": "temperature"
          }
        },
        "heater_temperature75cea6bd-21bb-4794-8314-0291a7c9ad71": {
          "avg": {
            "field": "battery_temperature"
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": ["*"],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    },
    {
      "field": "timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "timestamp": {
              "gte": 1644225612000,
              "lte": 1644315612000
            }
          }
        },
        {
          "match_phrase": {
            "asset_id.keyword": {
              "query": "75cea6bd-21bb-4794-8314-0291a7c9ad71"
            }
          }
        }
      ],
      "filter": [],
      "should": [],
      "must_not": []
    }
  }
}

query should get asset 1 and 3 but in my case i am getting asset 2 which i need to filter.

AssetId Timestamp                       temperature   battery_temperature
1      2022-02-07T06:31:37.000-05:00   10             null
2      2022-02-07T06:35:37.000-05:00   null           null
3      2022-02-07T06:40:37.000-05:00   null           20

CodePudding user response：

As you need to get document if atleast one field available then you can use should clause with exists query.

Below is query for your requirement:

{
  "query": {
    "bool": {
      "should": [
        {
          "exists": {
            "field": "temperature"
          }
        },
        {
          "exists": {
            "field": "battery_temperature"
          }
        }
      ]
    }
  }
}

Sample document which is in index:

{
"hits" : [
      {
        "_index" : "nullcheck",
        "_type" : "_doc",
        "_id" : "w9V43X4BGC8jFmD-T06n",
        "_score" : 1.0,
        "_source" : {
          "temperature" : 10,
          "battery_temperature" : 10
        }
      },
      {
        "_index" : "nullcheck",
        "_type" : "_doc",
        "_id" : "xNV43X4BGC8jFmD-Z06W",
        "_score" : 1.0,
        "_source" : {
          "temperature" : null,
          "battery_temperature" : 10
        }
      },
      {
        "_index" : "nullcheck",
        "_type" : "_doc",
        "_id" : "xdV43X4BGC8jFmD-hk4n",
        "_score" : 1.0,
        "_source" : {
          "temperature" : null,
          "battery_temperature" : null
        }
      }
    ]
}

Response for above query:

{
"hits" : [
      {
        "_index" : "nullcheck",
        "_type" : "_doc",
        "_id" : "w9V43X4BGC8jFmD-T06n",
        "_score" : 2.0,
        "_source" : {
          "temperature" : 10,
          "battery_temperature" : 10
        }
      },
      {
        "_index" : "nullcheck",
        "_type" : "_doc",
        "_id" : "xNV43X4BGC8jFmD-Z06W",
        "_score" : 1.0,
        "_source" : {
          "temperature" : null,
          "battery_temperature" : 10
        }
      }
    ]
}