Given a table with numeric columns A, X, and Y, I'd like to obtain an array of (X, Y) tuples for every value of A, as follows:
| A | Points |
|---|-------------------|
| 1 | [[1,3],[2,3],...] |
| 2 | [[3,1],[1,2],...] |
...
I attempted to do this with the following (query):
datatable(A:int, X:int, Y:int) [
1, 1, 3,
1, 2, 3,
2, 3, 1,
2, 1, 2
]
| summarize Points = make_list(pack_array(X, Y)) by A
However, it appears make_list flattens arrays (though I can't seem to find where this is documented), giving:
| A | Points |
|---|-----------|
| 1 | [1,3,2,3] |
| 2 | [3,1,1,2] |
There are of course alternatives, though none of them are particularly satisfying:
Pack the columns to a dictionary instead of an array:
| summarize Points = make_list(bag_pack_columns(X, Y)) by A
[{"X":1,"Y":3},{"X":2,"Y":3}]
Put the array into a dictionary with just one key:
| summarize Points = make_list(bag_pack("P", pack_array(X, Y))) by A
[{"P":[1,3]},{"P":[2,3]}]
Convert each packed array to string before aggregating:
| summarize Points = make_list(tostring(pack_array(X, Y)) by A
["[1,3]","[2,3]"]
Encode the tuple as a (non-JSON) string before aggregating:
| summarize Points = make_list(strcat(X, ",", Y)) by A
["1,3","2,3"]
The ony way I've found to get to the desired end result of nested arrays is to construct the outer array as a JSON string, and then parse it to bring it back to an array, which just... unpleasant:
| summarize Points = parse_json(strcat('[', strcat_array(make_list(tostring(pack_array(X, Y))), ","), ']')) by A
Is there a better way to achieve this?
(To be clear, all of the alternatives I mention work just fine for my purposes, at this point I'm mostly asking out of curiosity.)
Add an additional nesting level by using nested pack_array
datatable(A:int, X:int, Y:int) [
1, 1, 3,
1, 2, 3,
2, 3, 1,
2, 1, 2
]
| summarize Points = make_list(pack_array(pack_array(X, Y))) by A
| A | Points |
|---|---|
| 1 | [[1,3],[2,3]] |
| 2 | [[3,1],[1,2]] |