# Query and return aggs group by terms

**URL:** <https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290>\
**Category:** Elasticsearch\
**Created:** [May 26, 2020, 9:24am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290 "2020-05-26T09:24:48Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ivgenik](https://avatars.discourse-cdn.com/v4/letter/i/ce73a5/32.png) [@ivgenik](https://discuss.elastic.co/u/ivgenik)\
**Post date:** [May 26, 2020, 9:24am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/1 "2020-05-26T09:24:48Z")

</div>

Hello,

A minimal data example:

```auto
...
References: [
{
DocType: { SysName: "order"}
DocId: "12345"
},
{
"DocType: {SysName: "invoice"}
"DocId: "22222"}
]
MessageDate: "05/05/20"
...

```

I need to query this data, scan the references and return all the distinct doc Ids of type order while supporting paging and sorting if possible.

I am trying to figure out how to query this.  
So far I tried something like this :

```
{
   "aggs":{
      "docType":{
         "terms":{
            "field":"References.DocType.SysName.keyword"
         },
         "aggs": {
			"docId": {
	         	"terms": {
         		"field": "References.DocId.keyword",
         		"size": 10000
         		}
			}
         }
      }
   },
   "query":{
      "bool":{
	         "filter":[
            {
               "range":{
                  "MessageDate":{
                     "gte":"2015-05-24T00:00:00+03:00"
                  }
               }
            }
         ]
      }
   }
}

```

The response is :

```
"buckets": [
                {
                    "key": "order",
                    "doc_count": ...,
                    "docId": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": ...,
                        "buckets": [
                                {
                                    "key": "12345",
                                    "doc_count": 1
                                }]
...

```

This is sort of gives me what I need but I think I it is the wrong direction.  
Because I also need to support paging probably with the - from, size parameters.  
So Ideally I would have wanted to somehow return this information in the \_source, hits body.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 26, 2020, 9:39am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/2 "2020-05-26T09:39:53Z")

</div>

> [@ivgenik](#):
>
> This is sort of gives me what I need

`References.DocId.keyword : 22222 ` in the results is not of type `order`. Is that OK?  
You will probably need to use `nested` field mappings and aggregations to solve that confusion.

---

<div class="post-metadata">

**Author:** ![ivgenik](https://avatars.discourse-cdn.com/v4/letter/i/ce73a5/32.png) [@ivgenik](https://discuss.elastic.co/u/ivgenik)\
**Post date:** [May 26, 2020, 9:43am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/3 "2020-05-26T09:43:27Z")

</div>

Fixed

---

<div class="post-metadata">

**Author:** ![ivgenik](https://avatars.discourse-cdn.com/v4/letter/i/ce73a5/32.png) [@ivgenik](https://discuss.elastic.co/u/ivgenik)\
**Post date:** [May 26, 2020, 10:31am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/4 "2020-05-26T10:31:39Z")

</div>

I am trying to use something like -

```
"aggs": {
          			"top_docId": {
         			"top_hits": {
         				"size": 100,
         				"_source": { "includes": ["References.DocId", "References.DocType.SysName"] }
         			}
         		}

```

This is very close to what I need,  
But I am also facing a problem with this approach.  
I want to somehow make sure that the repsonse is going to include distinct order Doc Ids.

Is there a way to modify the response so instead of something like this :

```
"hits": [
                    {
                        "_index": "...",
                        "_type": "_doc",
                        "_id": "...",
                        "_score": 0.0,
                        "_source": {
                            "References": [
                                {
                                    "DocId": "...",
                                    "DocType": {
                                        "SysName": "order"
                                    }
                                },
                                {
                                    "DocId": "...",
                                    "DocType": {
                                        "SysName": "shipment"
                                    }
                                }
                            ]
                        }...

```

I will get a flattened response like this :

```
[
{
                                        "DocId": "...",
                                        "DocType": {
                                            "SysName": "order"
                                        }...
]

```

So basically without the header data, and if possibe without the other doc types data.  
I think if I will be able to receive this kind of a response I will also get the distinct option for free?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 26, 2020, 10:33am UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/5 "2020-05-26T10:33:07Z")

</div>

Are you using [nested](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) docs?

---

<div class="post-metadata">

**Author:** ![ivgenik](https://avatars.discourse-cdn.com/v4/letter/i/ce73a5/32.png) [@ivgenik](https://discuss.elastic.co/u/ivgenik)\
**Post date:** [June 15, 2020, 2:56pm UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/6 "2020-06-15T14:56:17Z")

</div>

I managed to solve this on my service logic.  
Thanks for the replay!

---

<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 13, 2020, 2:57pm UTC](https://discuss.elastic.co/t/query-and-return-aggs-group-by-terms/234290/7 "2020-07-13T14:57:19Z")

</div>

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