kql - Aggregate with make_list without flattening arrays - Stack Overflow

admin管理员组

文章数量:1400844

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.)

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.)

• kql
• azure-data-explorer

Share Improve this question Follow asked Mar 23 at 19:51 Philippe SignoretPhilippe Signoret 14.4k22 gold badges4444 silver badges6363 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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]]

本文标签： kqlAggregate with makelist without flattening arraysStack Overflow