Bucket Script - Sum Aggregation as filter

[karnamonkster]

Similar to this issue
I need to plot the devices which have (sum of value = 0) for an interval of 1hour split by device type
I have following fields

• dsn - Unique Device no
• cdate- Date Field
• cons- Consumption Value
• op - Device Code

I might be doing the bucket script incorrectly, but here is what i tried.
My json query input & result

POST index-2019.02.10/_search
{
     "aggs": {
    "zero_cons": {
      "date_histogram": {
        "field": "cdate",
        "interval": "hour"
      },
      "aggs": {
        "sum_cons": {
          "sum": {
            "field": "cons"
          }
        },
        "op_4type":{
          "filter": {
            "term": {
              "op.keyword": "4"
            }
          }
        },
        "op_3type":{
          "filter": {
            "term": {
              "op.keyword": "3"
            }
          }
        },
        "cons_bucket_filter":
        {
          "bucket_script": {
            "buckets_path": {
              "sumcons": "sum_cons"
            },
            "script": "params.sumcons = 0"
          }
         }
      }
      
  }
}
}

Result for Aggregation

"aggregations": {
    "zero_cons": {
      "buckets": [
        {
          "key_as_string": "2019-02-09T22:00:00.000Z",
          "key": 1549749600000,
          "doc_count": 105423,
          "op_3type": {
            "doc_count": 485
          },
          "op_4type": {
            "doc_count": 65923
          },
          "sum_cons": {
            "value": 972.3830020010937
          },
          "cons_bucket_filter": {
            "value": 0
          }
        },
        {
          "key_as_string": "2019-02-09T23:00:00.000Z",
          "key": 1549753200000,
          "doc_count": 920284,
          "op_3type": {
            "doc_count": 4723
          },
          "op_4type": {
            "doc_count": 749187
          },
          "sum_cons": {
            "value": 5888.251016019261
          },
          "cons_bucket_filter": {
            "value": 0
          }
        },
        {
          "key_as_string": "2019-02-10T00:00:00.000Z",
          "key": 1549756800000,
          "doc_count": 1186960,
          "op_3type": {
            "doc_count": 5619
          },
          "op_4type": {
            "doc_count": 1017752
          },
          "sum_cons": {
            "value": 7340.813020244357
          },
          "cons_bucket_filter": {
            "value": 0
          }
        }

And my Vega Code

{
  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
  "title": "Event counts from all indexes",
  "data": {
    "url": {
      "%context%": true,
      "%timefield%": "cdate",
      "index": "index-*",
      "body": {
     "aggs": {
    "zero_cons": {
      "date_histogram": {
        "field": "cdate",
        "interval": "hour"
      },
      "aggs": {
        "sum_cons": {
          "sum": {
            "field": "cons"
          }
        },
        "op_4type":{
          "filter": {
            "term": {
              "op.keyword": "4"
            }
          }
        },
        "op_3type":{
          "filter": {
            "term": {
              "op.keyword": "3"
            }
          }
        },
        "zerocons":
        {
          "bucket_script": {
            "buckets_path": {
              "sumcons": "sum_cons"
            },
            "script": "params.sumcons == 0? 1:0"
          }
         }
      }
      
  }
}
},
        "size": 100
        // _source: ["cdate","cons","dsn"]
      }
    
   // "format": {"property": "hits.hits"}
   format: {property: "aggregations.zero_cons.buckets"}
  }
  // transform: [{calculate: "toDate(datum._source['cdate'])", as: "time"}]
  "mark": "circle",
  "encoding": {
    "x": {"field": "key", "type": "temporal", "axis": {"title": "Meter" }},
    "y": {
      "field": "zerocons.value",
      "type": "quantitative",
      "axis": {"title": "Consumption"}
    },
    color: {
      "field": "op", type: "nominal", legend : {title:"Device"}
    },
    shape: {
      "field": "zerocons.value", type: "nominal"
    },
    size: {
      "field": "zerocons.value", type: "nominal", legend: null
    }
  }
}

This i believe is not correct, may be some help would be really appreciated

[nyurik]

@karnamonkster I won't be able to really help with the querying part - I am somewhat of a novice on that front too. You might want to ask on http://discuss.elastic.co just about the querying -- how to get the information you need. Once you get the data in the form you need, I could try to help with the plotting it using Vega. (you should probably ask the question on the discuss as well). In general, it is best to follow these steps to post Vega questions.

[karnamonkster]

Hi @nyurik ,

I am able to get the query, now i need to plot the devices in the vega visualization

POST /index-2019.03.17/_search
{
    "size": 0,
    "aggs" : {
        "cons_per_hour" : {
            "date_histogram" : {
                "field" : "cdate",
                "interval" : "hour"
            },
            "aggs": {
                "total_cons": {
                    "sum": {
                        "field": "cons"
                    }
                },
                "cons_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "totalCons": "total_cons"
                        },
                        "script": "params.totalCons < 0.001"
                    }
                }
            }
        }
    }
}

Here is the result

"aggregations": {
    "cons_per_hour": {
      "buckets": [
        {
          "key_as_string": "2019-03-14T16:00:00.000Z",
          "key": 1552579200000,
          "doc_count": 1,
          "total_cons": {
            "value": 0
          }
        },
        {
          "key_as_string": "2019-03-14T22:00:00.000Z",
          "key": 1552600800000,
          "doc_count": 1,
          "total_cons": {
            "value": 0
          }
        }
      ]
    }
  }