How to join/combine data from 2 indices using a common/join field in Elastic?

Hi,
Thank you for your time for reading the question. I have 2 indices:
Index 1 (Activation) - Order Id, Activation Date
Index 2 (Identity) - Order Id, Identity Date. The join field/common field is Order id.
I have to calculate the duration from Identity Date to Activation Date in minutes and plot a bar graph showing all the orders that were activated in 10 minutes on Kibana ? Could you please explain how we can do that ? Is there some transformation that needs to be applied at the time of indexing the data or we can directly do this on Kibana ?. I have pasted some sample data for your reference.

Activation:

Order Id Mode of registration Brand Activation Date Application name
101 Cust Care AVC 3/1/2022 0:02 ACT_01
102 Online website AVC 3/1/2022 0:02 ACT_01
103 Online website AVC 3/1/2022 0:02 ACT_01
104 Online website AVC 4/1/2022 0:30 ACT_01
105 Online website AVC 3/1/2022 0:02 ACT_01

Identity

Order Id Mode of registration Brand Identity Date Application name
101 Cust Care AVC 3/1/2022 0:00 IDV_02
102 Online website AVC 3/1/2022 0:00 IDV_02
103 Online website AVC 3/1/2022 0:00 IDV_02
104 Online website AVC 4/1/2022 0:00 IDV_02
105 Online website AVC 3/1/2022 0:00 IDV_02

Thank you very much, again !! It would be really help us if we could get some guidance here !!

Welcome to our community! :smiley:

Yes, this is the best way to do it in the Elastic Stack.
You can do that with an ingest pipeline that does an enrichment from one of the indices into the other.

Thank you @warkolm for the answer. I will definitely try that. I read on elastic documentation that for an enrich processor, one of the data sources (the one that contains enrich information) needs to be static. In my case both the source indices will be updated per second.
Please help me with this!!

Thank you again :blush:

What exactly do you need help with?

@warkolm
Apologies for not being clear with the question before. Below is a detailed explanation of the question:
Please revisit my first post where I listed down 2 indices. I want to join the Activation and Identity indices based on the Order Id field and calculate the duration from Identity Date till Activation Date. The output or Kibana Data view should look as below:

Order Id Mode of registration Brand Identity Date Application name Activation Date Duration (in minutes)
101 Cust Care AVC 03-01-2022 00:00 ACT_01 03-01-2022 00:02 2
102 Online website AVC 03-01-2022 00:00 ACT_01 03-01-2022 00:02 2
103 Online website AVC 03-01-2022 00:00 ACT_01 03-01-2022 00:02 2
104 Online website AVC 04-01-2022 00:00 ACT_01 04-01-2022 00:30 30
105 Online website AVC 03-01-2022 00:00 ACT_01 03-01-2022 00:02 2

Please note that both the source indices are dynamic. We need to create this result data for all the incoming documents in real time.
Then, we will use this resultant data set to create a dashboard in Kibana.
I need help with this implementation. I came across 2 options in my research:

  1. Writing transforms
  2. Enrich processor
    Could you please suggest which of these is a better option. We would also be interested if there is another way to solve this challenge.

Thank you again !!

You would be better off using an enrich processor here.

Thank you Very much @warkolm .. I really appreciate your quick responses :slightly_smiling_face:
I will read more on Enrich Processors and will come back if there are more questions.
Thank you very much!!