Kibana Filter Query

[ggajanan]

Hi,

I want to create a query filter for the following SQL query in Kibana (6.3.2)

SELECT requestuid
FROM blue-account-2018.08
group by requestuid
having count(requestuid) = 1

where,
blue-account-2018.08 = index name
requestuid = String data type field

Can somebody help, please?

Thank you!

DSL query executed on ES

[Joe_Fleming]

I'm getting an exception when I try using HAVING, and not a very useful one: [search_phase_execution_exception]. It's possible that it doesn't support HAVING. My SQL is super rusty, but I would expect WHERE COUNT(requestuid) = 1 to be basically the same query, but that doesn't work... it doesn't cause an exception, but it also doesn't filter any of the results.

Are you also getting an exception or is something else happening with that query?

[Andrei_Stefan]

@ggajanan additionally to @Joe_Fleming's question, can you also mention how you are running that query and, if possible, include the complete request?
Thanks

[Andrei_Stefan]

The only thing that comes to mind that won't work with the query you provided as is, is the escaping part. The query should be:

POST _xpack/sql?format=txt
{
  "query": "SELECT requestuid FROM \"blue-account-2018.08\" group by requestuid having count(requestuid) = 1"
}

[ggajanan]

Perfect! Thank you so much.