Joining Multiple Aggregration Queries into one query

prashant5375 · March 15, 2015, 6:02pm

Hi ,
I have One query given below .

{
"query": {
"filtered": {
"query": {
"query_string": {
"query": "*"
}
},
"filter": {
"bool": {
"should": [
{
"fquery": {
"query": {
"query_string": {
"query": ""357539019562335""
}
},
"_cache": true
}
},
{
"fquery": {
"query": {
"query_string": {
"query": ""357539019562624""
}
},
"_cache": true
}
}
],
"must": [
{
"fquery": {
"query": {
"query_string": {
"query": "SMSATTEMPTSMO + SMSATTEMPTSMT"
}
},
"_cache": true
}
},
{
"range": {
"@timestamp": {
"to": "2015-03-05T00:00-05:00",
"from": "2015-03-04T00:00-05:00"
}
}
}
]
}
}
}
},
"aggs": {
"events_by_IMEI": {
"terms": {
"field": "IMEI"
},
"aggs": {
"sms_over_time": {
"date_histogram": {
"field": "@timestamp",
"interval": "1h",
"post_zone": "-05:00",
"min_doc_count": 0,
"extended_bounds": {
"min": "2015-03-04T00:00-05:00",
"max": "2015-03-05T00:00-05:00"
}
},
"aggs": {
"total_sms": {
"sum": {
"field": "value"
}
}
}
}
}
}
},
"size": 0
}
Which gives me below response.

{
"took": 9279,
"timed_out": false,
"_shards": {
"total": 20,
"successful": 20,
"failed": 0
},
"hits": {
"total": 354,
"max_score": 0,
"hits": []
},
"aggregations": {
"events_by_IMEI": {
"buckets": [
{
"key": "357539019562624",
"doc_count": 178,
"sms_over_time": {
"buckets": [
{
"key_as_string": "2015-03-04T00:00:00.000Z",
"key": 1425427200000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T01:00:00.000Z",
"key": 1425430800000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T02:00:00.000Z",
"key": 1425434400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T03:00:00.000Z",
"key": 1425438000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T04:00:00.000Z",
"key": 1425441600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T05:00:00.000Z",
"key": 1425445200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T06:00:00.000Z",
"key": 1425448800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T07:00:00.000Z",
"key": 1425452400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T08:00:00.000Z",
"key": 1425456000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T09:00:00.000Z",
"key": 1425459600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T10:00:00.000Z",
"key": 1425463200000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T11:00:00.000Z",
"key": 1425466800000,
"doc_count": 8,
"total_sms": {
"value": 8
}
},
{
"key_as_string": "2015-03-04T12:00:00.000Z",
"key": 1425470400000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T13:00:00.000Z",
"key": 1425474000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T14:00:00.000Z",
"key": 1425477600000,
"doc_count": 8,
"total_sms": {
"value": 2
}
},
{
"key_as_string": "2015-03-04T15:00:00.000Z",
"key": 1425481200000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T16:00:00.000Z",
"key": 1425484800000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T17:00:00.000Z",
"key": 1425488400000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T18:00:00.000Z",
"key": 1425492000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T19:00:00.000Z",
"key": 1425495600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T20:00:00.000Z",
"key": 1425499200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T21:00:00.000Z",
"key": 1425502800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T22:00:00.000Z",
"key": 1425506400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T23:00:00.000Z",
"key": 1425510000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-05T00:00:00.000Z",
"key": 1425513600000,
"doc_count": 2,
"total_sms": {
"value": 0
}
}
]
}
},
{
"key": "357539019562335",
"doc_count": 176,
"sms_over_time": {
"buckets": [
{
"key_as_string": "2015-03-04T00:00:00.000Z",
"key": 1425427200000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T01:00:00.000Z",
"key": 1425430800000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T02:00:00.000Z",
"key": 1425434400000,
"doc_count": 6,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T03:00:00.000Z",
"key": 1425438000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T04:00:00.000Z",
"key": 1425441600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T05:00:00.000Z",
"key": 1425445200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T06:00:00.000Z",
"key": 1425448800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T07:00:00.000Z",
"key": 1425452400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T08:00:00.000Z",
"key": 1425456000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T09:00:00.000Z",
"key": 1425459600000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T10:00:00.000Z",
"key": 1425463200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T11:00:00.000Z",
"key": 1425466800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T12:00:00.000Z",
"key": 1425470400000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T13:00:00.000Z",
"key": 1425474000000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T14:00:00.000Z",
"key": 1425477600000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T15:00:00.000Z",
"key": 1425481200000,
"doc_count": 8,
"total_sms": {
"value": 10
}
},
{
"key_as_string": "2015-03-04T16:00:00.000Z",
"key": 1425484800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T17:00:00.000Z",
"key": 1425488400000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T18:00:00.000Z",
"key": 1425492000000,
"doc_count": 8,
"total_sms": {
"value": 2
}
},
{
"key_as_string": "2015-03-04T19:00:00.000Z",
"key": 1425495600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T20:00:00.000Z",
"key": 1425499200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T21:00:00.000Z",
"key": 1425502800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T22:00:00.000Z",
"key": 1425506400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T23:00:00.000Z",
"key": 1425510000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-05T00:00:00.000Z",
"key": 1425513600000,
"doc_count": 2,
"total_sms": {
"value": 0
}
}
]
}
}
]
}
}
}

So my question here is if i have to combine 2 or more such queries and expect the response in different-2 buckets of buckets , how
would we do that.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c46ab7b0-8f96-473a-b97f-99a9c9b6c370%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

prashant5375 · March 15, 2015, 6:04pm

Adding Reference to it.

gist.github.com

https://gist.github.com/parasb/751baf0fca2a7682a8d1

Facet Request

{
    "facets": {
        "0": {
            "facet_filter": {
                "fquery": {
                    "query": {
                        "filtered": {
                            "query": {
                                "query_string": {
                                    "query": "*"

This file has been truncated. show original

Facet Response

{
  "took": 389,
  "timed_out": false,
  "_shards": {
    "total": 10,
    "successful": 10,
    "failed": 0
  },
  "hits": {
    "total": 371712,

This file has been truncated. show original

aggregation request

{
    "query": {
        "filtered": {
            "query": {
                "query_string": {
                    "query": "*"
                }
            },
            "filter": {
                "bool": {

This file has been truncated. show original

There are more than three files. show original

On Sunday, March 15, 2015 at 11:32:20 PM UTC+5:30, Prashant singh wrote:

Hi ,
I have One query given below .

{
"query": {
"filtered": {
"query": {
"query_string": {
"query": "*"
}
},
"filter": {
"bool": {
"should": [
{
"fquery": {
"query": {
"query_string": {
"query": ""357539019562335""
}
},
"_cache": true
}
},
{
"fquery": {
"query": {
"query_string": {
"query": ""357539019562624""
}
},
"_cache": true
}
}
],
"must": [
{
"fquery": {
"query": {
"query_string": {
"query": "SMSATTEMPTSMO + SMSATTEMPTSMT"
}
},
"_cache": true
}
},
{
"range": {
"@timestamp": {
"to": "2015-03-05T00:00-05:00",
"from": "2015-03-04T00:00-05:00"
}
}
}
]
}
}
}
},
"aggs": {
"events_by_IMEI": {
"terms": {
"field": "IMEI"
},
"aggs": {
"sms_over_time": {
"date_histogram": {
"field": "@timestamp",
"interval": "1h",
"post_zone": "-05:00",
"min_doc_count": 0,
"extended_bounds": {
"min": "2015-03-04T00:00-05:00",
"max": "2015-03-05T00:00-05:00"
}
},
"aggs": {
"total_sms": {
"sum": {
"field": "value"
}
}
}
}
}
}
},
"size": 0
}
Which gives me below response.

{
"took": 9279,
"timed_out": false,
"_shards": {
"total": 20,
"successful": 20,
"failed": 0
},
"hits": {
"total": 354,
"max_score": 0,
"hits":
},
"aggregations": {
"events_by_IMEI": {
"buckets": [
{
"key": "357539019562624",
"doc_count": 178,
"sms_over_time": {
"buckets": [
{
"key_as_string": "2015-03-04T00:00:00.000Z",
"key": 1425427200000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T01:00:00.000Z",
"key": 1425430800000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T02:00:00.000Z",
"key": 1425434400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T03:00:00.000Z",
"key": 1425438000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T04:00:00.000Z",
"key": 1425441600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T05:00:00.000Z",
"key": 1425445200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T06:00:00.000Z",
"key": 1425448800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T07:00:00.000Z",
"key": 1425452400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T08:00:00.000Z",
"key": 1425456000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T09:00:00.000Z",
"key": 1425459600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T10:00:00.000Z",
"key": 1425463200000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T11:00:00.000Z",
"key": 1425466800000,
"doc_count": 8,
"total_sms": {
"value": 8
}
},
{
"key_as_string": "2015-03-04T12:00:00.000Z",
"key": 1425470400000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T13:00:00.000Z",
"key": 1425474000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T14:00:00.000Z",
"key": 1425477600000,
"doc_count": 8,
"total_sms": {
"value": 2
}
},
{
"key_as_string": "2015-03-04T15:00:00.000Z",
"key": 1425481200000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T16:00:00.000Z",
"key": 1425484800000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T17:00:00.000Z",
"key": 1425488400000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T18:00:00.000Z",
"key": 1425492000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T19:00:00.000Z",
"key": 1425495600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T20:00:00.000Z",
"key": 1425499200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T21:00:00.000Z",
"key": 1425502800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T22:00:00.000Z",
"key": 1425506400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T23:00:00.000Z",
"key": 1425510000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-05T00:00:00.000Z",
"key": 1425513600000,
"doc_count": 2,
"total_sms": {
"value": 0
}
}
]
}
},
{
"key": "357539019562335",
"doc_count": 176,
"sms_over_time": {
"buckets": [
{
"key_as_string": "2015-03-04T00:00:00.000Z",
"key": 1425427200000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T01:00:00.000Z",
"key": 1425430800000,
"doc_count": 0,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T02:00:00.000Z",
"key": 1425434400000,
"doc_count": 6,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T03:00:00.000Z",
"key": 1425438000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T04:00:00.000Z",
"key": 1425441600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T05:00:00.000Z",
"key": 1425445200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T06:00:00.000Z",
"key": 1425448800000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T07:00:00.000Z",
"key": 1425452400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T08:00:00.000Z",
"key": 1425456000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T09:00:00.000Z",
"key": 1425459600000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T10:00:00.000Z",
"key": 1425463200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T11:00:00.000Z",
"key": 1425466800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T12:00:00.000Z",
"key": 1425470400000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T13:00:00.000Z",
"key": 1425474000000,
"doc_count": 8,
"total_sms": {
"value": 3
}
},
{
"key_as_string": "2015-03-04T14:00:00.000Z",
"key": 1425477600000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T15:00:00.000Z",
"key": 1425481200000,
"doc_count": 8,
"total_sms": {
"value": 10
}
},
{
"key_as_string": "2015-03-04T16:00:00.000Z",
"key": 1425484800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T17:00:00.000Z",
"key": 1425488400000,
"doc_count": 8,
"total_sms": {
"value": 4
}
},
{
"key_as_string": "2015-03-04T18:00:00.000Z",
"key": 1425492000000,
"doc_count": 8,
"total_sms": {
"value": 2
}
},
{
"key_as_string": "2015-03-04T19:00:00.000Z",
"key": 1425495600000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T20:00:00.000Z",
"key": 1425499200000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T21:00:00.000Z",
"key": 1425502800000,
"doc_count": 8,
"total_sms": {
"value": 1
}
},
{
"key_as_string": "2015-03-04T22:00:00.000Z",
"key": 1425506400000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-04T23:00:00.000Z",
"key": 1425510000000,
"doc_count": 8,
"total_sms": {
"value": 0
}
},
{
"key_as_string": "2015-03-05T00:00:00.000Z",
"key": 1425513600000,
"doc_count": 2,
"total_sms": {
"value": 0
}
}
]
}
}
]
}
}
}

So my question here is if i have to combine 2 or more such queries and expect the response in different-2 buckets of buckets , how
would we do that.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/35dfbc81-a589-4b96-939a-3ceb5fecfa80%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Topic		Replies	Views
Multiple aggregation in single query or single single aggregation in multiple query which will perform better Elasticsearch	1	93	March 6, 2024
Aggregation Query filtering on results Elasticsearch	9	240	August 11, 2023
Aggregations - Average - based on some filters Elasticsearch	5	573	April 12, 2018
What is the best approach to combine multiple aggregations based in same key Elasticsearch	1	240	June 23, 2021
Can anyone convert array query to ejs aggregration query Elasticsearch	1	393	July 5, 2017

Joining Multiple Aggregration Queries into one query

Hi , I have One query given below .

Related topics

Hi ,
I have One query given below .