group by and having not working

[bigdatalyze]

select count(replyHistory.id),session_id from engine nested(replyHistory.raw.keyword) in ('Abc','abc','ABC') group by session_id having count(replyHistory.id) > 2

{"error":{"root_cause":[{"type":"parser_exception","reason":"not support token:IDENTIFIER"}],"type":"parser_exception","reason":"not support token:IDENTIFIER"},"status":500}

[shi-yuan]

Sorry, having isn't supported！

[mhsankar]

i have a solution to use having in elastisearch. but who can convert this code into elastic SQL Plugin format? answer link

[shi-yuan]

yeah，bucket_selector not supported now

[mhsankar]

when do you think add bucket_selector in SQL Plugin?

[ibc789]

什么时候 可以支持 bucket_selector？

[palbiplab]

when can we expect support for bucket_selector in SQL Plugin or Having?

[shi-yuan]

I really don’t know the exact date yet~

[shi-yuan]

select count(age) as ageCnt, avg(age) as ageAvg from bank group by gender having ageAvg >= 4 and ageCnt >= 5 order by ageCnt desc

{
  "from": 0,
  "size": 0,
  "_source": {
    "includes": [
      "COUNT",
      "AVG"
    ],
    "excludes": []
  },
  "aggregations": {
    "gender": {
      "terms": {
        "field": "gender",
        "size": 1000,
        "shard_size": 20000,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "ageCnt": "desc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "ageCnt": {
          "value_count": {
            "field": "age"
          }
        },
        "ageAvg": {
          "avg": {
            "field": "age"
          }
        },
        "having": {
          "bucket_selector": {
            "buckets_path": {
              "ageAvg": "ageAvg",
              "ageCnt": "ageCnt"
            },
            "script": {
              "source": "params.ageAvg >= 4\n&& params.ageCnt >= 5",
              "lang": "painless"
            },
            "gap_policy": "skip"
          }
        }
      }
    }
  }
}