# I have to combine certain aggregation and query to extract data from elastic search . Can someone help with this following query

**URL:** <https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746>\
**Category:** Elasticsearch\
**Created:** [July 3, 2020, 7:52am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746 "2020-07-03T07:52:03Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![charvi23](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/charvi23/32/103607_2.png) [@charvi23](https://discuss.elastic.co/u/charvi23)\
**Post date:** [July 3, 2020, 7:52am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/1 "2020-07-03T07:52:04Z")

</div>

From a set of documents do the following:

1. Extract documents which match the following criteria:  
a. date\_field \> given certain date and  
b. second\_date\_field \< given certain date

2. From the above extracted documents, take weekly data (week1 ,week2 etc..) .

3. As per above week interval and calculate in how many documents does a specific field value occurs.

Following is my index:

{  
"mappings": {  
"properties": {  
"insight\_id": {  
"type": "keyword"  
},  
"insight\_score": {  
"type": "keyword"  
},  
"insight\_min\_date": {  
"type": "date",  
"format": "dd-MM-yyyy HH:mm:ss||dd-MM-yyyy"  
},  
"insight\_max\_date": {  
"type": "date",  
"format": "dd-MM-yyyy HH:mm:ss||dd-MM-yyyy"  
},  
"topic": {  
"type": "keyword"  
}  
}  
}  
}

Expected output:

if input date range: 1/4/2019-1/5/2019

It should find documents where:

1. insight\_min\_date\>1/4/2019 and insight\_max\_date\<1/5/2019
2. For week 1 (1/4/2019-7/4/2019)  
{"topic": "good fat",  
"count":20}  
{"topic":"bad fat", "count":10}

Similarly for week 2, week 3 etc.

I tried combining date histogram,date range, aggregation still don't get desired output

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [July 3, 2020, 8:26am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/2 "2020-07-03T08:26:09Z")

</div>

> [@charvi23](#):
>
> As per above week interval and calculate in how many documents does a specific field value occurs.

Do I understand correctly? You want to query the aggregated data?

---

<div class="post-metadata">

**Author:** ![charvi23](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/charvi23/32/103607_2.png) [@charvi23](https://discuss.elastic.co/u/charvi23)\
**Post date:** [July 3, 2020, 8:28am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/3 "2020-07-03T08:28:41Z")

</div>

I want to aggregate the filtered data. According to desired output that I mentioned.

---

<div class="post-metadata">

**Author:** ![charvi23](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/charvi23/32/103607_2.png) [@charvi23](https://discuss.elastic.co/u/charvi23)\
**Post date:** [July 3, 2020, 8:54am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/5 "2020-07-03T08:54:18Z")

</div>

I want something like this:

```auto
GET /yogurt_insights/_search
{
 "query":{"bool":{"must" : [
        { "range" : { "insight_max_date" : { "lte" : "31-12-2019"} } },
        { "range" : { "insight_min_date" : {"gte":"11-10-2019" } }}
      ]
 }
 },"aggregations":{
   "agg":{"terms":{"field":"topic","order":[{"_count":"desc"}]},"aggregations":{"agg":{"date_histogram":{"field":"insight_max_date","interval":31104000000,"offset":0}}}}}
  }

But here I get data aggregated only by topic because of term query, I want it to be aggregated week wise for each topic separately.
```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [July 3, 2020, 9:18am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/6 "2020-07-03T09:18:07Z")

</div>

The sub-aggregation approach looks right to me, you first aggregate per topic, than per `date_histogram`, you could also do it the other way (more performant if your index is sorted using the timestamp).

Another alternative is a [composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html). The output of that might be less confusing.

Here is an example:

```auto
GET yogurt_insights/_search
{
  "query": {... your query ...}
  "size": 0,
  "aggs": {
    "t-dh": {
      "composite": {
        "sources": [
          {
            "top": {
              "terms": {
                "field": "topic"
              }
            }
          },
          {
            "dh": {
              "date_histogram": {
                "field": "insight_max_date",
                "calendar_interval": "1w"
              }
            }
          }
        ]
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![charvi23](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/charvi23/32/103607_2.png) [@charvi23](https://discuss.elastic.co/u/charvi23)\
**Post date:** [July 3, 2020, 9:44am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/7 "2020-07-03T09:44:19Z")

</div>

The output I get with my query is following.

```auto
    "agg" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Meal Replacement",
          "doc_count" : 2,
          "agg" : {
            "buckets" : [
              {
                "key_as_string" : "14-04-2019 00:00:00",
                "key" : 1555200000000,
                "doc_count" : 2
              }
            ]
          }
        },
        {
          "key" : "Sustainable",
          "doc_count" : 2,
          "agg" : {
            "buckets" : [
              {
                "key_as_string" : "14-04-2019 00:00:00",
                "key" : 1555200000000,
                "doc_count" : 2
              }
            ]
          }
        },
        {
          "key" : "Oats",
          "doc_count" : 1,
          "agg" : {
            "buckets" : [
              {
                "key_as_string" : "14-04-2019 00:00:00",
                "key" : 1555200000000,
                "doc_count" : 1
              }
            ]
          }
        }
      ]
    }
  }

The expected output should be something like this:

"buckets" : [
              {
                "key_as_string" : "14-04-2019 00:00:00",
                "key" : 1555200000000,
                agg: {
                    { "key" : "Sustainable"
                      "doc_count" : 2 }, {"key" : "Oats"
                      "doc_count" : 1"} 
                 }
              },
           {
                "key_as_string" : "14-04-2019 00:00:00",
                "key" : 1555200000000,
                agg: {
                    { "key" : "Sustainable"
                      "doc_count" : 2 }, {"key" : "Meal Replacement"
                      "doc_count" : 2"} 
                 }
              }
            ]

That is per week accumulated docs.
```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [July 3, 2020, 9:57am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/8 "2020-07-03T09:57:33Z")

</div>

You can switch the order and 1st aggregate per `date_histogram` and than `terms` (sub-aggregation), the result looks different but is internally equal.

---

<div class="post-metadata">

**Author:** ![charvi23](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/charvi23/32/103607_2.png) [@charvi23](https://discuss.elastic.co/u/charvi23)\
**Post date:** [July 3, 2020, 10:01am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/9 "2020-07-03T10:01:38Z")

</div>

I tried following which would solve my purpose, but just a small part remains. Can we set the first and last limit of histogram? I want this histogram to start from min date ("insight\_min\_date") and end on max date field ("insight\_max\_date").

```auto
{
"query":{"bool":{"must" : [
       { "range" : { "insight_max_date" : { "lte" : "31-12-2019"} } },
       { "range" : { "insight_min_date" : {"gte":"11-10-2019" } }}
     ]
}
},"aggregations":{
 "agg":{"terms":{"field":"topic","order":[{"_count":"desc"}]},"aggregations":{"agg":{"date_histogram":{"field":"insight_min_date","interval":"1w","offset":0,"format": "dd-MM-yyyy"}}}}}
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 31, 2020, 10:01am UTC](https://discuss.elastic.co/t/i-have-to-combine-certain-aggregation-and-query-to-extract-data-from-elastic-search-can-someone-help-with-this-following-query/239746/10 "2020-07-31T10:01:56Z")

</div>

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