SQL - Group values that begin with the same letters with horizontal bar chart in Canvas?

[lobotomeh]

Hello everyone,
I begin to work with the Canvas section in Kibana
What I try to do is to retrieve the count of several values ; and I need to group certain values together - the ones that start with the same letters.
I want to represent the result of the query in a Horizontal Bar Chart.

My SQL query looks like this :

SELECT 
(SELECT COUNT(*) FROM logs WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM logs WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM logs WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM logs WHERE status='anotherExactErrorValue')

When I test this query, using SQL and a little database, it works

This is my elasticsearch SQL query :

SELECT 
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*"
WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='anotherExactErrorValue')

And I get this error :

{
  "error": {
"message": "[essql] > Unexpected error from Elasticsearch: [unresolved_exception] Invalid call to nullable on an unresolved object ScalarSubquery[With[{}]
\\_Project[[?COUNT(?*)]]
\\_Filter[(status) REGEX (LikePattern)#5139]
 \\_UnresolvedRelation[[][index=monitoring-func-*],null,Unknown index [monitoring-func-*]],5142] AS ?"
  }
}

Seeing "unknown Index" , I first thought that the wildcard was the problem.

But it's not, it's perfectly fine in my others Elasticsearch queries.
And I don't see what the null object is.

Is there something about the Subqueries , the multiple SELECT , that Elasticsearch SQL doesn't handle well ? I didn't find any ressource or topics on this, but maybe I've searched the wrong way.

[Andrei_Stefan]

@lobotomeh the sub-selects in ES-SQL work only partially. They are listed as a limitation here. And the things that don't work with sub-selects is probably a open-ended list there. I'm pretty sure the error you get is related to expanding the wildcard inside the index name from the sub-select.