In our application, our primary use case is to create dynamic queries based on a user's input. Because of the complexity of standard elasticsearch queries, creating a dynamic SQL query is a much better approach.
However, when executing SQL queries, the result that's returned lacks some basic meta data, such as, the number of records returned on the query (thus making pagination nearly impossible, and the fact that we have a requirement were we must display the number of records found for a query).
Which leads me to the point where i have to have hundreds (thousands?) of lines of code to create a dynamic elasticsearch query.
If the xpack SQL plugin simply returned the "Hits" count, that's all we would need. Has someone figured out a workaround for this limitation with SQL queries?
I really don't want to query the database twice (once to get the count, and then a second time to get the rows) -- and i'd really like to avoid creating dynamic elasticsearch queries, and using the official Java client is a non starter in that bringing it into our project is like tossing a hand grenade into our dependencies. And Jest, well that's just writing tons of JSON strings, non starter.