Group Same fieldvalue documents and aggreate on different field values

[Vinothkumar_Ganeshan]

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"

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.

[matriv (Marios Trivyzas)]

To achieve the result, based on the way you have currently indexed your data, a join is required, something like:

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

[Vinothkumar_Ganeshan]

@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.

[matriv (Marios Trivyzas)]

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.