Print distinct values in an array

Question

I have a column which is an array and would like the print the distinct count

Event
| project colors

---

Present OutPut

["Red", "Green", "Green", "Red"]
["Yellow", "Yellow", "Yellow", "Yellow"]

---

Expected

["Red", "Green", "Green", "Red"] , 2
["Yellow", "Yellow", "Yellow", "Yellow"], 1

Answer 1

Here's what I think you want, using todynamic and mvexpand and summarize, (and datatable to create the input data)

// create your sample data using datatable to make a 'fake' table
datatable (colors: string) [
'["Red", "Green", "Green", "Red"]',
'["Yellow", "Yellow", "Yellow", "Yellow"]'
]
// this part is answering your question
| extend c = todynamic(colors) // turns colors into arrays
| mvexpand c // expands all the values out of colors into their own rows (but each column value is still "dynamic" type)
| extend c = tostring(c) // turn the dynamic c column to strings to summarize the values
| summarize ["Count"] = dcount(c) by colors // count up distinct c values by each row

that will output what you have in your question:

colors                                     Count 
------------------------------------------------
["Red", "Green", "Green", "Red"]           2 
["Yellow", "Yellow", "Yellow", "Yellow"]   1