How to join 2 index using 2 common values

[aannee (Ruby Ann)]

Hi, is it possible to join 2 index in elasticsearch? Maybe using query. Or I'm thinking if I could do a logstash pipeline that will query the two index and join them inside the pipeline.

First time to face a request like this. So, I don't know if this is possible or not.
Thank you!

[warkolm (Mark Walkom)]

You do need to use something external to query both indices and join them. There's nothing Elasticsearch can do for you on that front sorry.

[aannee (Ruby Ann)]

What I'm doing now is a logstash pipeline that queries the two indices. But the result is like this.

Just an example

message => {
  "took": ..........
}
{
   "took": ..........
}

As u can see, the result is in the message where in the result of the another index is appended in the first index's result.

I'm wondering if I can split this two result. And after that maybe do the filter split json. And will try a ruby code to join it.

Hehe, but I think its not possible?

Will explore more. Thank for replying!

[Hendrik_Muhs]

With transform you can query 2 or more indices and if they have a common field, like an id, you can "join" the results by grouping the common id.

To combine the documents you can use a scripted metric aggregation. It's possible to "collapse" the other documents, which means creating an array of all source documents, or you "join" them, meaning one overwrite the other one. For the later you have to decide the order.

Example post:

Combine multiple document into one document with limited Fields ( merging of documents )

You can use a transform for it. The group_by must be based on a common criteria, e.g. a common id. For the combining step, you can use a scripted metric, e.g.: "all_docs": { "scripted_metric": { "init_script": "state.docs = []", "map_script": "state.docs.add(new HashMap(params['_source']))", "combine_script": "return state.docs", "reduce_script": "def docs = []; for (s in states) {for (d in s) { docs.add(d);}}return docs" } } this would create an array with the original do…