Using sql query with parameters in dotnet client

[darooman (Geoff)]

When using the .net nuget package Elastic.Clients.Elasticsearch (version 8.1.3) to connect to an elastic cloud instance (running elastic v8.7.1), I am trying to use the sql query but I am struggling with the Params property on the QueryRequest. This property is a IDictionary<string, object> but if it is set, then Elasticsearch always seems to return an error.

I have found documentation on using sql syntax with the nuget package hard to find. The documentation here Introduction | Elasticsearch .NET Client [8.1] | Elastic doesn't mention the sql namespace at all.

The following code demonstrates the issue:

using Elastic.Clients.Elasticsearch;
using Elastic.Clients.Elasticsearch.Sql;
using Elastic.Transport;

const string CloudId = "MyCloudId";
const string ApiKey = "MyApiKey";

var client = new ElasticsearchClient(CloudId, new ApiKey(ApiKey));

// create simple sql query needing no parameters
var request1 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors GROUP BY machineName, username,message ORDER BY errorCount DESC",
    FetchSize = 100,
};

// this request works fine
var response1 = client.Sql.Query(request1);
Console.WriteLine(response1.DebugInformation);

// attempt to create a dictionary of parameters - no idea what the key should be as the rest client just wants an array
var parameters = new Dictionary<string, object>
{
    { "0",  DateTime.Now.Date },
};

var request2 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors WHERE dateAdded=? GROUP BY machineName, username,message ORDER BY errorCount DESC",
    Params = parameters,
    FetchSize = 100,
};

/* this request fails with:
Invalid Elasticsearch response built from a unsuccessful (400) low level call on POST: /_sql
 Exception: Request failed to execute. Call: Status code 400 from: POST /_sql. ServerError: Type: x_content_parse_exception Reason: "[1:207] [sql/query] params doesn't support values of type: START_OBJECT"
*/
var response2 = client.Sql.Query(request2);
Console.WriteLine(response2.DebugInformation);

// create request requiring no parameters but provide an empty dictionary
var request3 = new QueryRequest
{
    Query = "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors GROUP BY machineName, username,message ORDER BY errorCount DESC",
    FetchSize = 100,
    Params = new Dictionary<string, object>(),
};

// this request also fails with an empty dictionary
var response3 = client.Sql.Query(request3);
Console.WriteLine(response3.DebugInformation);

If I use postman, I can get the query to work with the _sql endpoint:

{ 
    "fetch_size": 100, 
    "query": "SELECT machineName,username,message, COUNT(1) AS errorCount FROM debug_errors WHERE dateAdded=? GROUP BY machineName, username,message ORDER BY errorCount DESC", 
    "params": [ "2023-07-14" ] 
}