Is it possibile to compare fields of different indexes and work on them?

[Marchese_De_Sade (Max)]

Hi everyone! First of all, I apologize for my bad english.
Now, the question! I'm new to Elastic and I'm following the online guide, but I still have a few unsolved doubts:

1. I have two different indexes: "jobs" and "stops" (data of a profiling machine).

2. Both indexes have begin/end DateTime fields, but those fields have different names ("jobs_date_begin", jobs_date_end" & "stops_date_begin", "stops_date_end").

3. Now I want to extract some data: every "job" might have one or more "stops" included in its DateTime range.

4. So, the query I need to do is something like this: [foreach record in "jobs" find me every record in "stops" whose "stops_date_begin" is included in DateTime range of "jobs_date_begin" & "jobs_date_end"]. Basically I need to perform a "Join" between JOBS and STOPS indexes based on the overlapping of these dates

5. How can I obtain this kind of result? I need a complex query? Or a script? Or what? I expect the result to be a JSON with a structure as follows (or something similar)

[
{
"name": "Job1"
"jobs_date_begin": "2016-02-07-10:15:00"
"jobs_date_stop": "2016-02-07-12:22:00"
"stops:"[
{
"stop_name": "Stop23",
"stops_date_begin": "2016-02-07-11:12:00",
"stops_date_end": "2016-02-07-11:14:00",
},
{
"stop_name": "Stop32",
"stops_date_begin": "2016-02-07-11:33:00",
"stops_date_end": "2016-02-07-11:38:00",
},
]
},
etc, other jobs will follow with the same structure
]

6. In order to achieve such result, is my data structure (two differents indexes) correct? Is a change of structure required?

7. I know I'm on the wrong forum, but (if it's possible to accomplish what I wrote before), how can I use this JSON result in Kibana in order to get an histogram?. Moreover is it possible to use this same JSON to build more complex histograms which perform some kind of aggregation on such data?
   I attach a paint demo of the histogram needed (a vertical histogram with X_Axis="job_id" and Y_Axis="stops_count").

   

Thank you very much,
Max

[s1monw (Simon Willnauer)]

hey, I think your english is more than fine and there is not the slightest reason to apologize!

Now to your question, You can NOT join across indices in elasticsearch but you can put your data into one index and add a type field to distinguish between the document types. Then you can use the join type to execute parent / childish queries for it, see: https://www.elastic.co/guide/en/elasticsearch/reference/6.1/parent-join.html