Errors using query inside sub-expression

[devbot]

I'm on Kibana 7.4

I'm trying to understand how to use queries inside sub-expressions. In canvas I have a markdown element that works perfectly fine with:

    filters group="default"
    | essql 
      query="select SUM(foo) as foo_total  from \"index*\"  "
    | head 1
    | if { context | head 1 | getCell "foo" | lte 100 } then={markdown "True" | render} else={markdown "False" | render}

But this doesn't work:

    filters group="default"
    | essql 
      query="select SUM(foo) as foo_total  from \"index*\"  "
    | head 1
    | if { context | head 1 | getCell "foo" | lte {essql query="select count(*) from \"index2*\" " | head 1 | getCell "count"}  } then={markdown "True" | render} else={markdown "False" | render}

I get the error "Expression Failed with the following message: [if] > [lte] > Can not cast number to any of filter"

What's an example of nesting my query inside the if then logic here that would actually work?

[lukeelmers]

I think the issue you are running into is that the essql function only accepts something of type filter as input (because it applies any global filters to the query), and the context is different by the time you get to the lte function (it's the number returned from getCell "foo"), so essql doesn't know what to do with it -- which explains the cryptic Can not cast number to any of filter error.

The reason most of our data fetching functions are set up this way is because they originally assumed that generally, fetching data was going to happen at the beginning of an expression instead of in the middle. Obviously this is an assumption we should revisit, because yours is a perfectly valid use case.

In the meantime, a workaround would be to use the var and var_set functions, which can be used to store globals that are accessible later in the expression. In your case, it would look something like this:

    filters group="default"
    | var_set name="index2Count" value={essql query="select count(*) from \"index2*\" " | head 1 | getCell "count"}
    | essql 
      query="select SUM(foo) as foo_total  from \"index*\"  "
    | head 1
    | if { context | head 1 | getCell "foo" | lte {var "index2Count"}  } then={markdown "True" | render} else={markdown "False" | render}

[lukeelmers]

Ah, I just re-read your post and realized you are on 7.4. Unfortunately, I can't think of a good workaround for this that would work in 7.4

var and var_set were introduced in 7.7