How to get ctx.payload from Visualize Data Table ( nested aggregations )

Elastic Stack Elasticsearch
,
1

Hi elastic community,

I create a Visualize Data Table to filter some data,

and got response what I expect.

then I try to use watcher send alert message when buckets doc_count greater then a value.

what is the correct way to get nest aggregations payload ? :sweat:

Thank you !

the condition block is :

  "condition": {
    "script": {
      "source": "return ctx.payload.aggregations.2.buckets.level>200.3.buckets.doc_count() > 120",
      "lang": "painless"
    }
  },

return a error message:

Cannot simulate watch

[script_exception] compile error, with { script_stack={ 0="... ons.2.buckets.level>200.3.buckets.doc_count() > 12 ..." & 1=" ^---- HERE" } & script="return ctx.payload.aggregations.2.buckets.level>200.3.buckets.doc_count() > 120" & lang="painless" }

Data Table request inspect :

{
  "aggs": {
    "2": {
      "filters": {
        "filters": {
          "level>200": {
            "bool": {
              "must": [],
              "filter": [
                {
                  "bool": {
                    "should": [
                      {
                        "range": {
                          "level": {
                            "gt": 200
                          }
                        }
                      }
                    ],
                    "minimum_should_match": 1
                  }
                }
              ],
              "should": [],
              "must_not": []
            }
          }
        }
      },
      "aggs": {
        "3": {
          "terms": {
            "field": "extra.platformId",
            "order": {
              "_count": "desc"
            },
            "size": 9999
          }
        }
      }
    }
  },
  "size": 0,
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": ",\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":7},\"962148\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"08:40\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 19:04:07\",\"game_date_timestamp\":1630024800,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u53f6\\u592b\\u6839\\u5c3c\\u00b7\\u5965\\u7c73\\u8f9b\\u65af\\u57fa\",\"tid_a\":\"\\u8c22\\u5c14\\u76d6\\u00b7\\u9a6c\\u7279\\u7ef4\\u5ef6\\u79d1\",\"lid\":11905,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":12},\"962230\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"08:45\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:31:46\",\"game_date_timestamp\":1630025100,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u5eb7\\u65af\\u5766\\u4e01\\u00b7\\u5965\\u5c14\\u6c99\\u79d1\\u592b\",\"tid_a\":\"\\u8c22\\u5c14\\u76d6\\u00b7\\u83ab\\u7f57\\u4f50\\u592b\",\"lid\":10536,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":17},\"962231\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"08:45\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:31:46\",\"game_date_timestamp\":1630025100,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u5965\\u5217\\u683c\\u00b7\\u7d22\\u675c\\u7d22\\u592b\",\"tid_a\":\"\\u4e9a\\u6b77\\u5c71\\u5927\\u00b7\\u6ce2\\u666e\\u7f57\\u57fa\",\"lid\":10536,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":17},\"962146\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"09:00\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:21:15\",\"game_date_timestamp\":1630026000,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u5fb7\\u7c73\\u7279\\u91cc\\u00b7\\u5fb7\\u96f7\\u6587\\u65af\\u57fa\",\"tid_a\":\"\\u74e6\\u8fea\\u59c6\\u00b7\\u79d1\\u9a6c\\u5c14\",\"lid\":11905,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":32},\"962235\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"09:00\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:31:46\",\"game_date_timestamp\":1630026000,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u7c73\\u54c8\\u4f0a\\u5c14\\u00b7\\u53e4\\u585e\\u592b\",\"tid_a\":\"\\u7c73\\u54c8\\u4f0a\\u5c14\\u00b7\\u5207\\u5c14\\u7eb3\\u592b\\u65af\\u57fa\",\"lid\":10536,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":32},\"962152\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"09:05\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 19:34:14\",\"game_date_timestamp\":1630026300,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u5fb7\\u7c73\\u7279\\u91cc\\u00b7\\u8d1d\\u5170\\u5947\\u514b\",\"tid_a\":\"\\u963f\\u7eb3\\u6258\\u5229\\u00b7\\u96f7\\u74e6\",\"lid\":11905,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":37},\"962143\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"09:10\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 19:04:07\",\"game_date_timestamp\":1630026600,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u9a6c\\u514b\\u8f9b\\u00b7\\u8428\\u9a6c\\u96f7\",\"tid_a\":\"\\u7ef4\\u514b\\u591a\\u00b7\\u8d1d\\u5fb7\\u91cc\\u4e18\\u514b\",\"lid\":11905,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":42},\"961861\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"08:30\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:34:32\",\"game_date_timestamp\":1630024200,\"play_count\":0,\"league_type\":\"0\",\"pit_away_id\":\"0\",\"pit_home_id\":\"0\",\"game_over\":\"\",\"tid_h\":\"\\u67cf\\u6797CC\\u968a\",\"tid_a\":\"SG\\u827e\\u56e0\\u6d77\\u7279\\u54c8\\u96f7\",\"lid\":16659,\"card_yellow_h\":\"\",\"card_yellow_a\":\"\",\"card_red_h\":\"\",\"card_red_a\":\"\",\"period\":\"today\",\"score_h\":\"\",\"score_a\":\"\",\"orig_id\":0,\"follow_id\":0,\"champion_group_id\":0,\"team_away_suffix_id\":0,\"team_home_suffix_id\":0,\"suffix_id\":0,\"family_play_count\":0,\"team_duel_name\":0,\"game_start_time\":2},\"961846\":{\"game_no\":\"\",\"game_day\":\"2021-08-27\",\"game_date\":\"2021-08-27\",\"game_time\":\"09:00\",\"game_type\":\"F1\",\"strong\":\"\",\"strong2\":\"\",\"strong_v\":\"\",\"strong2_v\":\"\",\"game_open\":\"Y\",\"midfield\":false,\"receive\":\"Y\",\"islive\":false,\"stop_time\":0,\"last_update_time\":\"2021-08-27 16:34:32\",\"game_date_timestamp\":1630026000,\"play_count\":0,\"leagudatetime",
      "format": "date_time"
    },
    {
      "field": "@timestamp",
      "format": "date_time"
    },
    {
      "field": "datetime",
      "format": "date_time"
    }
  ],
  "_source": {
    "excludes": []
  },
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "datetime": {
              "gte": "2021-09-10T01:18:00.000Z",
              "lte": "2021-09-10T02:18:32.416Z",
              "format": "strict_date_optional_time"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

Response

{
  "took": 234,
  "timed_out": false,
  "_shards": {
    "total": 84,
    "successful": 84,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 2861454,
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "2": {
      "buckets": {
        "level>200": {
          "3": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": 76,
                "doc_count": 33
              },
              {
                "key": 41,
                "doc_count": 3
              },
              {
                "key": 49,
                "doc_count": 3
              },
              {
                "key": 28,
                "doc_count": 2
              },
              {
                "key": 32,
                "doc_count": 2
              },
              {
                "key": 50,
                "doc_count": 2
              },
              {
                "key": 109,
                "doc_count": 2
              },
              {
                "key": 52,
                "doc_count": 1
              },
              {
                "key": 75,
                "doc_count": 1
              },
              {
                "key": 103,
                "doc_count": 1
              },
              {
                "key": 110,
                "doc_count": 1
              },
              {
                "key": 126,
                "doc_count": 1
              }
            ]
          },
          "doc_count": 132318
        }
      }
    }
  }
}
2

update another try：

in the input block:

i used other keyword instead of "2", "3", "level>200"

"input": {
    "search": {
      "request": {
        "search_type": "query_then_fetch",
        "indices": [
          "cloud-service-laravel-json-*"
        ],
        "rest_total_hits_as_int": true,
        "body": {
          "size": 0,
          "track_total_hits": true,
          "aggs": {
            "level": {
              "filters": {
                "filters": {
                  "greater": {
                    "bool": {
                      "must": [],
                      "filter": [
                        {
                          "bool": {
                            "should": [
                              {
                                "range": {
                                  "level": {
                                    "gt": 200
                                  }
                                }
                              }
                            ],
                            "minimum_should_match": 1
                          }
                        }
                      ],
                      "should": [],
                      "must_not": []
                    }
                  }
                }
              },
              "aggs": {
                "platform": {
                  "terms": {
                    "field": "extra.platformId",
                    "order": {
                      "_count": "desc"
                    },
                    "size": 9999
                  }
                }
              }
            }
          },

and result is:

"result": {
    "execution_time": "2021-09-13T06:10:14.939Z",
    "execution_duration": 189,
    "input": {
      "type": "search",
      "status": "success",
      "payload": {
        "_shards": {
          "total": 84,
          "failed": 0,
          "successful": 84,
          "skipped": 0
        },
        "hits": {
          "hits": [],
          "total": 3592192,
          "max_score": null
        },
        "took": 189,
        "timed_out": false,
        "aggregations": {
          "level": {
            "buckets": {
              "greater": {
                "doc_count": 166007,
                "platform": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                    {
                      "doc_count": 13,
                      "key": 32
                    },
                    {
                      "doc_count": 8,
                      "key": 76
                    },

now the condition block i used "compare":

  "condition": {
    "compare": {
      "ctx.payload.aggregations.level.buckets.greater.platform.buckets.doc_count" : {"gt":120} 
    }
  },

then watcher didn't return ctx.payload what i expect...

 "condition": {
      "type": "compare",
      "status": "success",
      "met": false,
      "compare": {
        "resolved_values": {
          "ctx.payload.aggregations.level.buckets.greater.platform.buckets.doc_count": null
        }
      }
    },
    "actions": []
  },
  "messages": []
}
3

Hey,

the reason is, that you did not specify which bucket in the second buckets you wanted to retrieve... at which position. You could use sth like buckets.0.doc_count, but maybe switching to a script condition allows you to check for more than one bucket.

Hope that helps!

--Alex

4

Hi, Alex

Now I understand that i need to specify bucket.

and I change my condition like:

"condition": {
    "compare": {
      "ctx.payload.aggregations.level.buckets.greater.platform.buckets.0.doc_count": {
        "gt": 5
      }
    }
  },

it only return the 0 buckets result.

then I rewrite in script condition

"exception": {
    "type": "script_exception",
    "reason": "runtime error",
    "script_stack": [
      "if (ctx.payload.aggregations.level.buckets.greater.platform.buckets.0.doc_count() > 0 )  ",
      "                                                                     ^---- HERE"
    ],
    "script": "if (ctx.payload.aggregations.level.buckets.greater.platform.buckets.0.doc_count() > 0 )  return true",
    "lang": "painless",
    "caused_by": {
      "type": "illegal_argument_exception",
      "reason": "dynamic method [java.util.HashMap, doc_count/0] not found",
      "stack_trace": "java.lang.IllegalArgumentException: dynamic method [java.util.HashMap, doc_count/0] not found\n\tat org.elasticsearch.painless.Def.lookupMethod(Def.java:205)\n\tat

look like can't specify bucket ?

and if I need to compare multiple buckets doc_count ,

the * mark seems not support.

buckets.*.doc_count

what syntax should I use ?

thank you.

5

If you need to compare multiple buckets, you need to write a piece of code checking each bucket. Check https://github.com/elastic/examples/tree/master/Alerting for a few script examples.

1 Like
6

Hi Alex,

thanks your suggestion.

I find another way to compare multiple buckets.

aggregations block:

add a value like, "min_doc_count": 120,

          "aggs": {
            "level": {
              "filters": {
                "filters": {
                  "greater": {
                    "bool": {
                      "must": [],
                      "filter": [
                        {
                          "bool": {
                            "should": [
                              {
                                "range": {
                                  "level": {
                                    "gt": 200
                                  }
                                }
                              }
                            ],
                            "minimum_should_match": 1
                          }
                        }
                      ],
                      "should": [],
                      "must_not": []
                    }
                  }
                }
              },
              "aggs": {
                "platform": {
                  "terms": {
                    "field": "extra.platformId",
                    "min_doc_count": 120,
                    "order": {
                      "_count": "desc"
                    },
                    "size": 9999
                  }
                }
              }
            }
          },

In condition block:

add array_compare like,

  "condition": {
    "array_compare": {
      "ctx.payload.aggregations.level.buckets.greater.platform.buckets": {
        "path": "doc_count",
        "gt": {
          "value": 120,
          "quantifier": "some"
        }
      }
    }
  },

It will compare all the buckets. :grinning_face_with_smiling_eyes:

7

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