Fetching data from 3 index based on one unique field

[rohitarorait82 (Rohit arora)]

Hi All,

I have a requirement where I have three indexes

1. In my first index there are three fields (id,name,age)
2. In my second index, there are three fields(id, experience,company code)
3. In my third index , there are three fields(company code, company address, company name)

In the output , I am looking for a report where I can see
id,name,age,experience,company code,company name,company address

Here id is the unique key between first and second index, and company code is the unique key between second and third index.

Is it possible to achieve in elasticsearch

[rohitarorait82 (Rohit arora)]

I tried below query to fetch data from first two indexes by creating an alias as well, but that is also not working

POST _sql?format=txt
    {
      "query": """
     SELECT MAX(age.keyword),MAX(cc.keyword),MAX(exp.keyword),MAX(name.keyword),id.keyword from indexes GROUP by id.keyword
      """
    }

I am getting below error

{
  "error" : {
    "root_cause" : [
      {
        "type" : "no_such_element_exception",
        "reason" : null
      }
    ],
    "type" : "no_such_element_exception",
    "reason" : null
  },
  "status" : 500
}

[Christian_Dahlqvist]

What you are describing is a join which Elasticsearch does not support. You will therefore likely need to restructure, e.g. denormalize, your data.