I have an Elasticsearch cluster, which e.g. contains an index called persons. I want to query the documents of the index using the SQL API of Elasticsearch. When using the REST API of Elasticsearch via Kibana everything works fine:
POST /_sql?format=csv
{
"query": "SELECT * FROM persons"
}
However, I want to execute this query within a .NET Web API project. Therefore, I am using a .NET client for Elasticsearch (Elastic.Clients.Elasticsearch 8.1.0). I have already set up and configured the client, which worked fine. Now I am trying to execute the same query as mentioned above using the Elasticsearch .NET client like this:
var response = await _elasticsearchClient.Sql
.QueryAsync(q => q.Query("SELECT * FROM persons"));
When executing this query, Elasticsearch returns a valid response. However, this response seems to contain no results (even though the index persons contains documents). To be more precise, I can only access the returned columns and the cursor but not the returned rows.
Am I missing something or does the Elastic.Clients.Elasticsearch 8.1.0 client not fully support the SQL API?
Turned out that this was a bug related to code generation from the Elastic.Clients.Elasticsearch 8.1.0 .NET client. For more details see this discussion. This bug is addressed by this issue on GitHub and will be fixed in version 8.1.1.