Sql Query to get data from more than one Indices

[Resington_R]

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
}

[dadoonet (David Pilato)]

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

[Resington_R]

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

[bogdan.pintea]

Using the standard search API would work, but there's a way with SQL too, by using index patterns:

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.

[Resington_R]

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

[Resington_R]

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' "
}