CODEWITHSUNDEEP
sqlelasticsearch
stack
stack

Reputation: 13

Elasticsearch filter after aggregation

I want convert sql query to ES query.

This is my sql query

SELECT * FROM 
(SELECT order_number, MIN(log_datetime) as log_datetime 
FROM t_log 
WHERE mall_id='amazon' AND action_name='order_register' AND log_level='3' 
GROUP BY order_number) as temp
WHERE log_datetime BETWEEN '2018-11-16 00:00:00' AND '2018-11-16 23:59:59';

and my es query

{
  "size": 0,
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "mall_id": "devsdkwms1001"
              }
            },
            {
              "term": {
                "action_name": "order_register"
              }
            },
            {
              "term": {
                "log_level": 3
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "temp": {
      "range": {
        "field": "log_datetime",
        "ranges": [
          {
            "from": "2018-11-16 00:00:00",
            "to": "2018-11-16 23:59:59"
          }
        ]
      },
      "aggs": {
        "result": {
          "terms": {
            "field": "order_number",
            "size": 0
          }
        }
      }
    }
  }
}

My es query.. it doesn't work properly. I can't find a way to filter the aggregate results in Elastic Search. Only can aggregate after filter. Is there any way? Thank you

Upvotes: 1

Views: 11636

Answers (1)

LeBigCat
LeBigCat

Reputation: 1770

If you want filter agg result take a look to bucker selector: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html

Upvotes: 3

Related Questions