Is it possible to "join" two indexes in elasticsearch using query

user_12163451 0
Viewed 1238

I know that there is no option to join indexes in elasticsearch but I need to find a way to solve this problem:

I have 2 indexes like A, B

IndexA have informations like field1, field2, field3
IndexB have field4, field5, field6

And if i will search by the query field5(in this case "test") I want to have all relationships in the tree like:

Match all the the documents from IndexA which matches "field2" - from IndexA and "field5" from IndexB

For example
IndexA documents:
5, "test", "test2",
10, "test", "test7"
11, "test10", "test11"

IndexB documents:
1, "test", (...)
2, "test", (...)
3, "test100", (...)

The example response:
for id5 (from indexA) i want to have an object with id's 1 and 2 from indexB like {id:5, responses: {1, 2}}
for id10 (from IndexA) i want to have an object with id's 1 and 2 from indexB like {id:10, responses: {1, 2}}
for id11 there is no match ("test10" != "test")
{id:11, responses:{}}

Meybe there is any way to solve this? Finally I need to do this for four indexes (but if it is possible between two then I can do it on 4 aswell).

elasticsearch aggregation
2 Answers

I don't think it's possible in elasticsearch, just like you said. You shouldn't create indexes with such relations. It would be better to rethink your model and denormalize the data.

In order to solve this, you'll have to do the processing programmatically in your backend. Pseudocode: 

//Get all objects from indexA
const allIndexA = indexA.getAll();
const result = new Array();
//For each object in indexA, select the corresponding object in indexB
allIndexA.forEach((entryA) => {
    const entriesB = indexB.get({field5: entryA.field2});
    result.push({
        entryA,
        entriesB
    });
});
user_31257310 
                I was tring as bellow:

                GET /_msearch
                {
                  "_index": [
                    "index1",
                    "index2",
                    "index3"
                  ]
                }
                {
                  "query": {
                    "bool": {
                      "should": [
                        {
                          "match": {
                            "index3id": "1"  // it is in the 3th index so i have responses from 3th index
                          }                  // only
                        }                                           
                      ]
                    }
                  },
                  "size": 100,
                  "aggregations": {
                    "firstLevel": {
                      "top_hits": {
                        "size": 100,
                        "_source": {
                          "includes": "index3id"
                        }
                      }
                    }
                  }
                }

                response of aggregation here:

                 "aggregations": {
                   "firstLevel": {
                          "hits": {
                            "total": 2,
                            "max_score": 1,
                            "hits": [
                              {
                                "_index": "index3",
                                "_type": "someTypeNotRelevant",
                                "_id": "81",
                                "_score": 1,
                                "_source": {
                                  "index3id": 1
                                }
                              },
                              {
                                "_index": "index3",
                                "_type": "someTypeNotRelevant",
                                "_id": "61",
                                "_score": 1,
                                "_source": {
                                  "index3id": 1
                                }
                              }
                            ]
                          }
                        }
                      }

    Now I just want to do a new query in index2 for some field but with values which were in 
    _source(in this case - for the all index3id's) (i was thinking about some sub-aggregation to firstLevel": {} aggregation - but with use of new query to index2). 
There are 2 problems:
1. How to pass these index3id's?
2. After first query, I have only "data" from index3 because of using index3id
Anyway thank you for advice.
user_121634510
Related