CODEWITHSUNDEEP
kql
igorc
igorc

Reputation: 2034

Output multiple summarized lists with KQL

I want to output multiple lists of unique column values with KQL.

For instance for the following table:

A B C
1 x one
1 x two
1 y one

I want to output

K V
A [1]
B [x,y]
C [one, two]

I accomplished this using summarize with make_list and 2 unions, been wondering if its possible to accomplish this in the same query without union?

Table
| distinct A
| summarize k="A", v= make_list(A)
union
Table
| distinct b
| summarize k="B", v= make_list(B)
...

Upvotes: 0

Views: 2531

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

if your data set is reasonably-sized, you could try using the narrow() plugin: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/narrowplugin

datatable(A:int, B:string, C:string)
[
    1, 'x', 'one',
    1, 'x', 'two',
    1, 'y', 'one',
]
| evaluate narrow()
| summarize make_set(Value) by Column
Column set_Value
A ["1"]
B ["x","y"]
C ["one","two"]

Alternatively, you could use a combination of pack_all() and mv-apply

datatable(A:int, B:string, C:string)
[
    1, 'x', 'one',
    1, 'x', 'two',
    1, 'y', 'one',
]
| project p = pack_all()
| mv-apply p on (
    extend key = tostring(bag_keys(p)[0])
    | project key, value = p[key]
)
| summarize make_set(value) by key
key set_value
A ["1"]
B ["x","y"]
C ["one","two"]

Upvotes: 2

Related Questions