Joining More than 2 Indices

Hi,

Is there any way to do join more than 2 indices?

Cheers,
Michael

Hi,

You can use the Elasticsearch’s syntax for writing index patterns. Also, the indices field that you join to is an array.

GET /siren/indexa,indexb/_search
{
   "query": {
      "join": {
         "indices": [
            "index1",
            "index2",
            "index_pattern"
         ],
         "on": [
            "fk",
            "id"
         ],
         "request": {
            "query": {
               "match_all": {}
            }
         }
      }
   }
}

The join query being part of the Elasticsearch’s query DSL, you can include as you wish it in other Elasticsearch queries such as bool. You can also nest joins within the request field.

@Stephane_Campinas
Can you explain more? How two indexes related to each other for joining?
From where can i execute this query? If this query is execute via Postman?

Thank you!

In the query above, fk is a field of indexa and indexb, while id is a field of index1, index2, and index_pattern. That join query will return you all the documents from indexa and indexb where you’ve got fk == id matching.
You can execute that query as you would any other Elasticsearch query, but pay attention that the path is prefixed with /siren.

1 Like

@Stephane_Campinas Thanks.

As i understand -
There is Indexa and Indexb needs to join with index1, index2 and index_pattern.
in response, i will get data of indexa and indexb.

is it correct?
Also, i want to make sure one more things, If i need to join index1, and index2 and need to data of both index in response. What is a better way to achieve this? Those data in millions , scroll API is supported for same? (or better way to achieve this?)

Also, Aggregation is supported? I need the sum of field name total from 2 indexes (index1 and index2). Is it achievable?

Once again thank you for helping me.

yes.

With a single join query it is not yet possible to get data from index2, just from index1, but the 10.4 release will support this use case. At the moment, you need to do the opposite join to get the data from index2.

Scroll API is supported.

The join query is like any other Elasticsearch query, e.g., term or range. So you create the subset you are interested in and then compute the buckets.
However, with the previous answer you cannot use data from index2. Can you elaborate on that particular use case ?