# Group Same fieldvalue documents and aggreate on different field values
**URL:** https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086
**Category:** Elasticsearch
**Created:** [October 17, 2019, 3:18pm UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086 "2019-10-17T15:18:20Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Vinothkumar\_Ganeshan](https://discuss.elastic.co/u/Vinothkumar_Ganeshan)
#### Post date: [October 17, 2019, 3:18pm UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086/1 "2019-10-17T15:18:20Z")
I have two indexes t-click and t-impression. I created a common index as t\*.
index t-click
tid: "sdalfk-234k"
event: "click"
tid: "sdalfk-234k"
event: "click"
index t-impression
tid: "sdalfk-234k"
site: "[google.com](http://google.com)"
Now, I have to aggregate the websites of click by mapping tid of click with impression index and get the site field. I imagine it as where condition in SQL but I'm aware its not same. I struck here, do let me know, if you need more information.
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [October 18, 2019, 11:44am UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086/2 "2019-10-18T11:44:23Z")
To achieve the result, based on the way you have currently indexed your data, a join is required, something like:
```auto
SELECT count("t-click".event) FROM "t-click" , "t-impression" WHERE "t-click".tid = "t-impression.tid" WHERE event = 'click' GROUP BY "t-impression".site
```
Unfortunately joins are not supported by ES-SQL or ES search api.
You'll need to restructure and re-index your data by including the site on every event document.
You may want to take a look at parent-child relationships too: [https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html)
---
### Author:  [@Vinothkumar\_Ganeshan](https://discuss.elastic.co/u/Vinothkumar_Ganeshan)
#### Post date: [October 23, 2019, 2:51pm UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086/3 "2019-10-23T14:51:25Z")
@matriv Thanks for making it clear. In parent-child relation-ship I'm not very clear because in a index, there are hundreds of documents, many have duplicate id (field) of documents, in which some fields include the site field and some other not. So, I want to retrieve the field "Site" by searching all the documents of same id. Am I clear? I even tried to reindex for a certain period but only the limited documents are covered with the information. Thanks for your feedback.
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [October 30, 2019, 10:51pm UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086/4 "2019-10-30T22:51:28Z")
Yeah, for your data I don't think you the parent-child relationship can help you.
Instead you'd need to index the `site` on every document in `t-click` instead of the `tid` (I guess the `site` itself is unique). if `t-impression` has no other data it can be removed.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 27, 2019, 10:51pm UTC](https://discuss.elastic.co/t/group-same-fieldvalue-documents-and-aggreate-on-different-field-values/204086/5 "2019-11-27T22:51:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.