KQL Can't handle empty strings when using column_ifexists

Question

So I'm trying to create a function that dynamically projects a column from the source table based on a string columnName field, and I found the column_ifexits function which seems to (mostly) meet my needs. So if I do something like this:

MyTable (FooA:string, FooB:string, FooC:string)

.create-or-alter function GetFoo(columnName:string = 'FooA')
{
    MyTable
    | project Foo = column_ifexists(columnName, FooA)
}

This mostly works. If I pass a valid field name to GetFoo(), I get a projection of just that column from the source table. If I pass an invalid field name, or omit the field name paramter, I get a projection of the FooA column from the source table. Great. The only problem is, if I pass an empty string to the GetFoo() function, I get an exception:

column_ifexists(): argument #1 must be a non-empty string literal

So I figure, "okay, that should be relatively simple to fix," and I make the following adjustment:

.create-or-alter function GetFoo(columnName:string = 'FooA')
{
    let columnNameFixed = iff(isempty(columnName), 'FooA', columnName);
    MyTable
    | project Foo = column_ifexists(columnNameFixed, FooA)
}

But now, no matter what I pass in to the function, I get this exception:

column_ifexists(): argument #0 must be string literal

I tried wrapping the iff() call in a call to toscalar() which the docs specifically say should return a constant, but that's apparently not good enough. So how can I possibly fix this function to properly handle an empty string input? And if anyone know why column_ifexists doesn't just automatically treat the empty string input as a non-existent column name and return the specified default column, I'd love to hear about it.

user_4126914 · Answer

Your attempt didn't work because columnNameFixed is a variable, not a string literal.

One solution is to define the two branches using a fuzzy union, as follows:

.create-or-alter function GetFoo(columnName:string = 'FooA')
{
    MyTable
    | project Foo = column_ifexists(columnName, FooA)
    | union isfuzzy=true (
        MyTable
        | project FooA
        | where isempty(columnName)
    )
}

If columnName is not empty, the first branch of the union will work, and the second branch will have no results.

If columnName is empty, the first branch of the union will fail, and the second branch will return the FooA column, as you wanted.

For more info on fuzzy unions, see the documentation here: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/unionoperator?pivots=azuredataexplorer.

user_18428148 · Answer

As column_ifexists() is not having the habit of handling the empty string eventhough if we try to alter the table query (KQL), it will not take the query upto the execution point. Even we use isempty() or even alter the statement, as it is not having the habit of considering the empty string, it will through an error. Checkout the documentation for the reference.

KQL Can't handle empty strings when using column_ifexists

2 Answers