# Fetching data from 3 index based on one unique field

**URL:** <https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047>\
**Category:** Elasticsearch\
**Created:** [November 11, 2020, 11:13am UTC](https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047 "2020-11-11T11:13:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rohitarorait82](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rohitarorait82/32/82981_2.png) [@rohitarorait82](https://discuss.elastic.co/u/rohitarorait82)\
**Post date:** [November 11, 2020, 11:13am UTC](https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047/1 "2020-11-11T11:13:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rohitarorait82](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rohitarorait82/32/82981_2.png) [@rohitarorait82](https://discuss.elastic.co/u/rohitarorait82)\
**Post date:** [November 11, 2020, 12:33pm UTC](https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047/2 "2020-11-11T12:33:12Z")

</div>

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
}
```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 11, 2020, 12:48pm UTC](https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047/3 "2020-11-11T12:48:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 9, 2020, 12:48pm UTC](https://discuss.elastic.co/t/fetching-data-from-3-index-based-on-one-unique-field/255047/4 "2020-12-09T12:48:06Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.