CODEWITHSUNDEEP
azure-data-explorerkql
James Turner
James Turner

Reputation: 81

Is it possible in Kusto to only return columns with values?

I have an Azure data explorer table that contains the values of property fields for objects in my source database. The table has rows for different types of object, so not all columns are applicable to each object type.

I'd like to run queries to show the data for objects, but only project the columns that are populated with values and not the columns that are not applicable. So I won't know the column names at the time of querying as they are being triggered by an action that only contains the object name, not type, or schema.

Upvotes: 3

Views: 4218

Answers (2)

Brad Wilson
Brad Wilson

Reputation: 1

I've used isnotempty: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/isnotemptyfunction There is also isnotnull.

Upvotes: 0

Avnera
Avnera

Reputation: 7608

Here is a way to achieve this (credit Alex):

datatable(col1:string, col2: string , col3:int) 
[ 
 'aa', '', 5,
 'cc', 'dd', int(null)
]
| where col1=="aa"
| as T
| extend values = pack_all()
| mv-apply values on 
(
    mv-expand kind = array  values
    | where isnotempty(values[1])
    | summarize EmptyValuesRemoved = make_bag(pack(tostring(values[0]), values[1]))
)
| project EmptyValuesRemoved
| evaluate bag_unpack(EmptyValuesRemoved)

Results:

enter image description here

In case the data was ingested as json, you can save the original object in a column of its own by mapping the root object (use the "$" notation for that), then you just need to return that column.

Upvotes: 1

Related Questions