[ES|QL] Add "ANY" option to named parameters for filtering use case

[stratoula]

Description

One of the most common use case for ES|QL controls is filtering selected panels in a dashboard. Right now users are forced to select one or more values in a ES|QL control, they cannot completely remove the filter and select "Any" value as they can do with their DSL controls.

Example:

FROM logs* | WHERE host == ?host

Values in the ES|QL control:

• Any
• host 1
• host 2
• etc.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[elasticsearchmachine]

Pinging @elastic/kibana-esql (ES|QL-ui)

[alex-spies]

Hm, I don't know if this can be built into named params, at least easily. We'd have to understand what "no parameter" means in all places where a named param is allowed. For instance, WHERE host != ?host - if the param is missing, would that mean that no documents match?

Alternatively, can't the first case be solved with WHERE host LIKE ?host? If we map Any to *, that'd do it, no?

The STATS BY is trickier; but the query is issued by Kibana - can't we remove the BY clause in case of no breakdown?

[stratoula]

I created this because we had discussed it at some point and Shay had also suggested it. It is good to track it I think.

can't the first case be solved with WHERE host LIKE ?host? If we map Any to *, that'd do it, no?

Like doesnt support named params but when it will it would support some of the cases yes but not all of them.

[teresaalvarezsoler]

Alternatively, can't the first case be solved with WHERE host LIKE ?host? If we map Any to *, that'd do it, no?

@alex-spies apart from ANY we also need to support multi-selection for the filtering use case. Will the LIKE parameter support an array of values?

We are now allowing multi-selection with MV_CONTAINS so ideally we want that function to support ANY. Is that possible?

[alex-spies]

LIKE only works on single values.

Do I understand correctly: if a user selects val1,val2 in the control's dropdown, this is used as param in WHERE MV_CONTAINS(?host, some_field) and expands to WHERE MV_CONTAINS([val1, val2], some_field), right?

And if no values are selected in the drop down, we'd want this to be expanded to WHERE true, essentially?

[teresaalvarezsoler]

Do I understand correctly: if a user selects val1,val2 in the control's dropdown, this is used as param in WHERE MV_CONTAINS(?host, some_field) and expands to WHERE MV_CONTAINS([val1, val2], some_field), right?

That's correct.

And if no values are selected in the drop down, we'd want this to be expanded to WHERE true, essentially?

Pobrably... if WHERE true means "any" value then yes. "Any" in controls include both "exists" and "blank" values for a given field. It's like removing the WHERE clause essentially.