# Sql Query to get data from more than one Indices

**URL:** <https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [June 7, 2021, 12:15pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147 "2021-06-07T12:15:55Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Resington\_R](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/resington_r/32/85143_2.png) [@Resington\_R](https://discuss.elastic.co/u/Resington_R)\
**Post date:** [June 7, 2021, 12:15pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/1 "2021-06-07T12:15:55Z")

</div>

Hi...

I am using the below query to get the data from 2 Indices, but I am getting this error "Queries with multiple indices are not supported". myindex1 and myindex2 structures are identical. I am using Elasticsearch Ver 7.10.2 under Windows 10. Can some one please help me...Thanks in advance.

> THIS IS MY QUERY

POST /\_sql?format=json  
{  
"query": "select field1, field2, field3 from myindex1, myindex2 where field1='DATA1' "  
}

> THIS IS MY ERROR

{  
"error" : {  
"root\_cause" : [  
{  
"type" : "mapping\_exception",  
"reason" : "Queries with multiple indices are not supported"  
}  
],  
"type" : "mapping\_exception",  
"reason" : "Queries with multiple indices are not supported"  
},  
"status" : 400  
}

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [June 7, 2021, 12:42pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/2 "2021-06-07T12:42:16Z")

</div>

Sounds like you can't do it using the sql endpoint but you could do it using the standard search API.

---

<div class="post-metadata">

**Author:** ![Resington\_R](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/resington_r/32/85143_2.png) [@Resington\_R](https://discuss.elastic.co/u/Resington_R)\
**Post date:** [June 7, 2021, 1:01pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/3 "2021-06-07T13:01:04Z")

</div>

Thanks for your immediate reply... Since I knew sql better than Elastic query I was thought of doing all my queries using sql.

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [June 7, 2021, 2:48pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/4 "2021-06-07T14:48:00Z")

</div>

Using the standard search API would work, but there's a way with SQL too, by using [index patterns](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-syntax-select.html#sql-syntax-from):

```auto
select field1, field2, field3 from "myindex*" where field1='DATA1'

```

As long as the mappings aren't conflicting (ex: same field name defined of incompatible types in different indices), SQL will "merge" the mappings and query all matching indices.

---

<div class="post-metadata">

**Author:** ![Resington\_R](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/resington_r/32/85143_2.png) [@Resington\_R](https://discuss.elastic.co/u/Resington_R)\
**Post date:** [June 7, 2021, 4:33pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/5 "2021-06-07T16:33:27Z")

</div>

Thanks for your reply... I have tried your code... Its not working, can you please tell me where I am doing wrong.... I have tried with double quotes and single quotes too... Please correct this code...

```
POST /_sql?format=json
{  
  "query" : select field1, field2, field3 from "myindex*" where field1='DATA1'
}
```

---

<div class="post-metadata">

**Author:** ![Resington\_R](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/resington_r/32/85143_2.png) [@Resington\_R](https://discuss.elastic.co/u/Resington_R)\
**Post date:** [June 7, 2021, 5:03pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/6 "2021-06-07T17:03:54Z")

</div>

I have found the solution with the help of **Bogdan's** answer. This may be useful for someone like me.....

```
POST /_sql?format=json
{
"query": "select field1, field2, field3 from \"myindex*\" where field1='DATA1' "
}
```

---

<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:** [July 5, 2021, 5:04pm UTC](https://discuss.elastic.co/t/sql-query-to-get-data-from-more-than-one-indices/275147/7 "2021-07-05T17:04:24Z")

</div>

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