# \[Resolved\]Join data cross two index based on common field **URL:** \ **Category:** Elasticsearch\ **Created:** [August 16, 2019, 8:49am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458 "2019-08-16T08:49:52Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [August 16, 2019, 8:49am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/1 "2019-08-16T08:49:52Z")
Hi Team, In my local elk stack, below two index are created. 1. kvaudit\* - This one is index customer behavior data. Example data: ` > module=SCM fa=TS **at=SCM.TS.MODIFY\_SEARCH** si=4C3D8709E51DDC4EE879A9E30729B512.mo-5692ea7ca ci=SCMStella cn=SCMStella cs=qacandrot\_SCMStella. pi=dbPool1 ui=cgrant1 locale=en\_US ktf1=[C,E,X,H,M] ` 1. testcase\* - This one is index test case. Example data: > caseid, classname,features, **at** ,author,module > ENT200021808,Verifythatmodifysearchbuttonworksasexpected,TS, **SCM.TS.MODIFY\_SEARCH** ,I348636,SCM There is a common fields call 'at' in both index. My requirement is to join the data of these 2 index based on **at** field to setup the connection between customer behavior data and test case, then view in kibana. Is it achievable and any solution? ---
**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:** [August 16, 2019, 4:22pm UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/2 "2019-08-16T16:22:34Z")
See [here](https://discuss.elastic.co/t/how-to-merge-two-indexes-based-on-common-field-in-elasticsearch/194114/3) for creating a third index from the union of two existing indices. ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [August 19, 2019, 3:21am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/3 "2019-08-19T03:21:16Z")
Hi Mark, I tried, but the result is not what I expected. In the third index, it just append the data of second index into the first index, instead of join based on common field like we join two tables in SQL. ---
**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:** [August 19, 2019, 6:31am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/4 "2019-08-19T06:31:14Z")
Sounds like a bug in your client code. The pseudo code should be: Issue search on srcIndex1,srcIndex2 sorted by id for all results If current doc id== last doc id add current fields to last doc else write last doc to new index last doc = current doc ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [August 20, 2019, 2:48am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/5 "2019-08-20T02:48:29Z")
Hi Mark, I am not very understood the logic of the pseudo code here and a bit confused. At the beginning, I thought that create a 3rd index is enough, and no need to anything. 1. The pseudo code means data processing code in the logstash configuration file? 2. And both 2 index has no id field 3. the relationship between two indexes is m:m, instead of 1:1. that is 1 behavior event could map to multiple test case events, and 1 test case event could map to multiple behavior events. Is this can be doable as well? Thanks, Cherie ---
**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:** [August 20, 2019, 6:44am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/6 "2019-08-20T06:44:06Z")
Hi Cherie > [@cheriemilk](#): > > The pseudo code means data processing code in the logstash configuration file No, it means code as in Python, Perl, Java or whatever is your preferred programming language. I’m not sure this is something Logstash can do but should be a simple python script for example. > [@cheriemilk](#): > > index has no id field I believe you called it ‘at’? > [@cheriemilk](#): > > 1 test case event could map to multiple behavior events. The logic in my pseudo code should cater for that. ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [August 22, 2019, 2:41am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/7 "2019-08-22T02:41:51Z")
Hi Mark, Let me double confirm your solution. Do you mean that i export the data from two index, and use any programming language like python to join these data based on 'at', then I have a file unioned behavior data and test case, then use logstash to parse the unioned file and ingest the data into elasiticseach? hmmm.. if so, why need to create the 3rd index to union two parts of data? because the 'join' already completed by python at the beginning. Thanks, Cherie ---
**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:** [August 22, 2019, 6:59am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/8 "2019-08-22T06:59:32Z")
> [@cheriemilk](#): > > then I have a file No, the Python script can write directly to your new index (using the ‘bulk’ api) rather than writing to a file. Logstash is not required in this scenario - it’s normally used as a way of avoiding programming. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 30, 2019, 6:12am UTC](https://discuss.elastic.co/t/resolved-join-data-cross-two-index-based-on-common-field/195458/10 "2019-09-30T06:12:39Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.