make_set() (aggregation function)

Learn how to use the make_set() function to return a JSON array of the distinct values that the expression takes in the group.

Creates a dynamic array of the set of distinct values that expr takes in the group.

Syntax

make_set(expr [, maxSize])

Parameters

NameTypeRequiredDescription
exprstring✔️The expression used for the aggregation calculation.
maxSizeintThe maximum number of elements returned. The default and max value is 1048576.

Returns

Returns a dynamic array of the set of distinct values that expr takes in the group. The array’s sort order is undefined.

Example

Set from a scalar column

The following example shows the set of states grouped with the same amount of crop damage.

StormEvents 
| summarize states=make_set(State) by DamageCrops

The results table shown includes only the first 10 rows.

DamageCropsstates
0[“NORTH CAROLINA”,“WISCONSIN”,“NEW YORK”,“ALASKA”,“DELAWARE”,“OKLAHOMA”,“INDIANA”,“ILLINOIS”,“MINNESOTA”,“SOUTH DAKOTA”,“TEXAS”,“UTAH”,“COLORADO”,“VERMONT”,“NEW JERSEY”,“VIRGINIA”,“CALIFORNIA”,“PENNSYLVANIA”,“MONTANA”,“WASHINGTON”,“OREGON”,“HAWAII”,“IDAHO”,“PUERTO RICO”,“MICHIGAN”,“FLORIDA”,“WYOMING”,“GULF OF MEXICO”,“NEVADA”,“LOUISIANA”,“TENNESSEE”,“KENTUCKY”,“MISSISSIPPI”,“ALABAMA”,“GEORGIA”,“SOUTH CAROLINA”,“OHIO”,“NEW MEXICO”,“ATLANTIC SOUTH”,“NEW HAMPSHIRE”,“ATLANTIC NORTH”,“NORTH DAKOTA”,“IOWA”,“NEBRASKA”,“WEST VIRGINIA”,“MARYLAND”,“KANSAS”,“MISSOURI”,“ARKANSAS”,“ARIZONA”,“MASSACHUSETTS”,“MAINE”,“CONNECTICUT”,“GUAM”,“HAWAII WATERS”,“AMERICAN SAMOA”,“LAKE HURON”,“DISTRICT OF COLUMBIA”,“RHODE ISLAND”,“LAKE MICHIGAN”,“LAKE SUPERIOR”,“LAKE ST CLAIR”,“LAKE ERIE”,“LAKE ONTARIO”,“E PACIFIC”,“GULF OF ALASKA”]
30000[“TEXAS”,“NEBRASKA”,“IOWA”,“MINNESOTA”,“WISCONSIN”]
4000000[“CALIFORNIA”,“KENTUCKY”,“NORTH DAKOTA”,“WISCONSIN”,“VIRGINIA”]
3000000[“CALIFORNIA”,“ILLINOIS”,“MISSOURI”,“SOUTH CAROLINA”,“NORTH CAROLINA”,“MISSISSIPPI”,“NORTH DAKOTA”,“OHIO”]
14000000[“CALIFORNIA”,“NORTH DAKOTA”]
400000[“CALIFORNIA”,“MISSOURI”,“MISSISSIPPI”,“NEBRASKA”,“WISCONSIN”,“NORTH DAKOTA”]
50000[“CALIFORNIA”,“GEORGIA”,“NEBRASKA”,“TEXAS”,“WEST VIRGINIA”,“KANSAS”,“MISSOURI”,“MISSISSIPPI”,“NEW MEXICO”,“IOWA”,“NORTH DAKOTA”,“OHIO”,“WISCONSIN”,“ILLINOIS”,“MINNESOTA”,“KENTUCKY”]
18000[“WASHINGTON”,“WISCONSIN”]
107900000[“CALIFORNIA”]
28900000[“CALIFORNIA”]

Set from array column

The following example shows the set of elements in an array.

datatable (Val: int, Arr1: dynamic)
[
    1, dynamic(['A1', 'A2', 'A3']), 
    5, dynamic(['A2', 'C1']),
    7, dynamic(['C2', 'A3']),
    5, dynamic(['C2', 'A1'])
] 
| summarize Val_set=make_set(Val), Arr1_set=make_set(Arr1)
Val_setArr1_set
[1,5,7][“A1”,“A2”,“A3”,“C1”,“C2”]

Related content