Is there a KQL query to limit the number of sub results I get per a particular category?

Question

I’m trying to generate a query where I limit the number of sub results I get per a particular category, and could use some help on if there is a good function for this.

Quick Example:

|     ID    |     Category    |     Value    |     A bunch of other important columns    |  
|-----------|-----------------|--------------|-------------------------------------------|
|     1     |     A           |     GUID     |                                           |   
|     2     |     A           |     GUID     |                                           |   
|     3     |     A           |     GUID     |                                           |   
|     4     |     A           |     GUID     |                                           |   
|     5     |     B           |     GUID     |                                           |   
|     6     |     B           |     GUID     |                                           |

I want to return only N GUIDs per category. (Largely because I’m hitting the 64MB Kusto query limits for some Categories that won’t be useful anyway)

The Top-nested operator looks good at first, BUT I don’t want to do any aggregation, and it filters out other important columns. Per the note on the page, I can use Ignore=max(1) to remove the aggregation, then do some serializing of all my other columns to a certain value, then unpack after the filter. But that feels like I’m doing something very wrong.

I've also tried something like:

| partition by Category ( top 3 by Value)

But it's limited to 64 partitions, and I need closer to 500.

Any idea of a good pattern to do this?

user_2145097 · Answer

Here you go:

let NumItemsPerCategory = 3;
datatable(ID:long, Category:string, Value:guid)
[
    1, "A", guid(40b73f8f-78d2-4eae-bd5b-b3e00f38ac33), 
    2, "A", guid(043ee507-aadf-4453-bcc6-d8f4f541b043), 
    3, "A", guid(f71d3cc0-ce46-474f-9dcd-f3883fa08859), 
    4, "A", guid(bf259fc8-e9fe-4a99-a296-ca81e1fa250a), 
    5, "B", guid(d8ee3ac7-da76-4e87-a9ed-e5a37c943ad2), 
    6, "B", guid(282e74ff-3b71-407c-a2a7-92bb1cb17b27), 
]
| summarize PackedItems = make_list(pack_all(), NumItemsPerCategory) by Category
| project-away Category
| mv-expand PackedItem = PackedItems
| evaluate bag_unpack(PackedItem)
| project-away PackedItems

Result:

| ID | Category | Value                                |
|----|----------|--------------------------------------|
| 1  | A        | 40b73f8f-78d2-4eae-bd5b-b3e00f38ac33 |
| 2  | A        | 043ee507-aadf-4453-bcc6-d8f4f541b043 |
| 3  | A        | f71d3cc0-ce46-474f-9dcd-f3883fa08859 |
| 5  | B        | d8ee3ac7-da76-4e87-a9ed-e5a37c943ad2 |
| 6  | B        | 282e74ff-3b71-407c-a2a7-92bb1cb17b27 |

Is there a KQL query to limit the number of sub results I get per a particular category?

1 Answers