Iterate by rows on an essql query

Elastic Stack Kibana
1

Hello,
I'm using essql queries.
I need to iterate the result by row.

I just found the way to iterate it on markdown. But I need to iterate on canvas, not in markdown.
Because using markdown #each, I can't use if conditions like:
rows.country='UK'

Just like:
| essql query="Select name from index"
for (r in rows) {
r.name
}

Is it possible to do that?

Thanks in advance.
Luis.

2

Hi @luis.montecatine,

Yes, that's possible in canvas, but the syntax is different - you are basically chaining functions separated by | and the data passes through them from the first one to the last one. You can look through all functions in the documentation: https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html

For your specific example (using conditions), you can use filterrows:

filters
| essql 
  query="Select name, country from index"
|  filterrows {getCell "country" | eq "UK"}
| table
| render

This will query the index index for the name and the country field and then filter out all rows that don't have the country value UK. You can do this specific example completely within the essql query (Select name, country from index where country = "UK"), but the provided functions are very flexible because you can arrange and nest them freely.

3

Hello Joe,
First of all, thanks for the answer.

I tried to use filterrows too but I think I won't works for me.
My goal is to iterate the query rows in order to only do one query at the beginning and then apply some different filters (exclusive between then).
And then use markdown to show then.

I tried used mapColumn too in order to use then like filters and then use markdown conditionals to print then.
I haven't tried too much but I think is my best option at this moment. Any alternative?
Maybe can I use filterrows and clean, or It'll be very inefficient?

Thanks for all.
Luis

4

I'm not sure I completely understand your use case, could you elaborate on what you are trying to achieve from a user perspective and how your data roughly looks like?

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.