ElasticSearch - Aggregation + Range + Term query

Question

With ElasticSearch, I would like to get the sum of a field for a period with filtering on a value of an other field.

I defined this mapping:

PUT match-orders
{
    "settings" : {
        "number_of_shards" : 1,
        "number_of_replicas" : 0
    },
    "mappings": {
        "order": {
            "_all": {"enabled": false},
            "properties": {
                "matchTime": {"type": "date", "index": "true"},
                "product_id": {"type": "keyword", "index": "true"},
                "size": {"type": "float", "index": "true"},
                "price": {"type": "float", "index": "true"},
                "side": {"type": "keyword", "index": "true"}
            }
        }
    }
}

I can get the sum for a range:

POST /match-orders/_search?pretty
{
    "aggs" : {
    "price_ranges" : {
            "range" : {
                "field" : "matchTime",
                "ranges" : [
                    { "from" : "2017-09-10T18:00:00Z", "to" : "2017-09-10T18:15:00Z" }
                ]
            },
            "aggs" : {
                "result" : { "sum" : { "field" : "size" } }
            }
        }
    }
}

I can get the documents which contain a specific term:

POST /match-orders/_search?pretty
{
    "query": {
        "term" : { "side" : "sell" } 
    }
}

But how can I combine these two queries?

Thank you :)

Val · Accepted Answer · 2017-09-20 14:42:58Z

5

You simply need to merge your query with your aggregation like this:

POST /match-orders/_search?pretty
{
    "query": {
        "term" : { "side" : "sell" } 
    },
    "aggs" : {
        "price_ranges" : {
            "range" : {
                "field" : "matchTime",
                "ranges" : [
                    { "from" : "2017-09-10T18:00:00Z", "to" : "2017-09-10T18:15:00Z" }
                ]
            },
            "aggs" : {
                "result" : { "sum" : { "field" : "size" } }
            }
        }
    }
}

answered Sep 20, 2017 at 14:42

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

admin · Accepted Answer · 2020-09-18 07:13:43Z

0

{
  "aggs" : {
    "all_xxx" : {
      "terms" : { "field" : "xxx", "size" : 1000 }
    },
    "custom_range" : {
      "range" : { "field" : "datetime", "ranges" : [{ "from" : "2020-09-11 12:06:27" }, { "to" : "now" }] }
    }
  },
  size: 0
}

answered Sep 18, 2020 at 7:13

admin

1791 silver badge6 bronze badges

1 Comment

Mathieu Rollet Over a year ago

This query returns both the aggregation for documents matching "terms" and the aggregation for documents matching "range", but do not combine the two conditions (documents matching "terms" AND "range")

Rajeev Ranjan · Accepted Answer · 2017-09-20 14:54:30Z

-1

Following interpolation of the terms query should do -

POST /match-orders/_search?pretty
{
    "aggs" : {
        "price_ranges" : {
            "range" : {
                "field" : "matchTime",
                "ranges" : [
                    { "from" : "2017-09-10T18:00:00Z", "to" : "2017-09-10T18:15:00Z" }
                ]
            },
            "terms" : {
                 "field" : "side",
                 "include" : ["sell"]
             },
            "aggs" : {
                "result" : { "sum" : { "field" : "size" } }
            }
        }
    }
}

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_filtering_values_with_exact_values

answered Sep 20, 2017 at 14:54

Rajeev Ranjan

4,1767 gold badges33 silver badges58 bronze badges

2 Comments

Swayvil Over a year ago

This query returns an error: "root_cause": [ { "type": "parsing_exception", "reason": "Found two aggregation type definitions in [price_ranges]: [range] and [terms]", "line": 10, "col": 23 } ]

Shaz Hemani Over a year ago

This query is invalid, range and term can not be added like this

Collectives™ on Stack Overflow

ElasticSearch - Aggregation + Range + Term query

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related