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

user_14993870 0
Viewed 1987

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.

kql azure-data-explorer
2 Answers

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.

user_101679140
user_153324340
Related