Edit

Share via

make_list() (aggregation function)

  • Article

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Creates a dynamic array of all the values of expr in the group.

Null values are ignored and don't factor into the calculation.

Note

This function is used in conjunction with the summarize operator.

Deprecated aliases: makelist()

Syntax

make_list(expr [, maxSize])

Learn more about syntax conventions.

Parameters

Name Type Required Description
expr dynamic ✔️ The expression used for the aggregation calculation.
maxSize int The maximum number of elements returned. The default and max value is 1048576.

Note

The deprecated version has a default maxSize limit of 128.

Returns

Returns a dynamic array of all the values of expr in the group. If the input to the summarize operator isn't sorted, the order of elements in the resulting array is undefined. If the input to the summarize operator is sorted, the order of elements in the resulting array tracks that of the input.

Tip

Use the array_sort_asc() or array_sort_desc() function to create an ordered list by some key.

Examples

The examples in this section show how to use the syntax to help you get started.

One column

The following example uses the datatable, shapes, to return a list of shapes in a single column.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name)

Output

mylist
["triangle","square","rectangle","pentagon","hexagon","heptagon","octagon","nonagon","decagon"]

Using the 'by' clause

The following example uses the make_list function and the by clause to create two lists of objects grouped by whether they have an even or odd number of sides.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| summarize mylist = make_list(name) by isEvenSideCount = sideCount % 2 == 0

Output

isEvenSideCount mylist
false ["triangle","pentagon","heptagon","nonagon"]
true ["square","rectangle","hexagon","octagon","decagon"]

Packing a dynamic object

The following examples show how to pack a dynamic object in a column before making it a list. It returns a column with a boolean table isEvenSideCount indicating whether the side count is even or odd and a mylist column that contains lists of packed bags int each category.

let shapes = datatable (name: string, sideCount: int)
[
    "triangle", 3,
    "square", 4,
    "rectangle", 4,
    "pentagon", 5,
    "hexagon", 6,
    "heptagon", 7,
    "octagon", 8,
    "nonagon", 9,
    "decagon", 10
];
shapes
| extend d = bag_pack("name", name, "sideCount", sideCount)
| summarize mylist = make_list(d) by isEvenSideCount = sideCount % 2 == 0

Output

isEvenSideCount mylist
false [{"name":"triangle","sideCount":3},{"name":"pentagon","sideCount":5},{"name":"heptagon","sideCount":7},{"name":"nonagon","sideCount":9}]
true [{"name":"square","sideCount":4},{"name":"rectangle","sideCount":4},{"name":"hexagon","sideCount":6},{"name":"octagon","sideCount":8},{"name":"decagon","sideCount":10}]

Related content