Trying to get a list of objects in Kusto Table Rows using make-series

Question

I'm trying to write a Kusto Query on an Azure Resource to gather individual page views.

My current query looks like this:

let startTime = datetime(${someTime});
let endTime = datetime(${someOtherTime});

pageViews
| where client_Type == 'Browser' and cloud_RoleName == '${someCloudName}'
| make-series count(itemCount) default = 0 on timestamp from startTime to endTime step ${someTimespan}
| mv-expand timeStamp = timestamp, value = count_itemCount
| project rows = pack('timeStamp', timeStamp, 'value', value)

And it produces the following result:

{
  "metrics": {
    "tables": [
      {
        "name": "PrimaryResult",
        "columns": [
          {
            "name": "rows",
            "type": "dynamic"
          }
        ],
        "rows": [
          [
            {
              "timeStamp": "2024-01-01T07:51:20.3540000Z",
              "value": 23
            }
          ],
          [
            {
              "timeStamp": "2024-01-02T07:51:20.3540000Z",
              "value": 142
            }
          ],
          // etc. etc.
        ],
        "columnDescriptors": [
          {
            "name": "rows",
            "type": "dynamic"
          }
        ]
      }
    ],
    "status": "Success"
  }
}

However, for my API I would like the data (or rows) to be in a list of objects, not a list of list of objects, like this:

"rows": 
  [
    {
      "timeStamp": "2024-01-01T07:51:20.3540000Z",
      "value": 23
     },
     {
       "timeStamp": "2024-01-02T07:51:20.3540000Z",
       "value": 142
     }
     // etc. etc.
]

Can this be done just with modifying the query above or would I have to do it programmatically?
Sorry if this is a stupid question, I could not find anything that helped me.

I could also get the data to be in a list of lists, by using the above query without the last line, but I would really prefer the list of objects, as mentioned above.

Doing it programmatically would also not be an issue but I would prefer a solution with just the query, also I am trying to learn more about KQL doing this 😊

Thanks in advance for responding.

Answer 1

One way you could use would be to format the output using string manipulation you specify. make_set(), strcat() and replace_string() could be useful.

let T = datatable(timeStamp:datetime, value:int) [
datetime('2024-01-01T07:51:20.3540000Z'), 23,
datetime('2024-01-02T07:51:20.3540000Z'), 142
];
T
| extend Message = strcat(
    '{ "timestamp": "', timeStamp, '", "value": "', value, '" }' 
    )
| summarize Rows = make_set(Message)
| summarize by Output = strcat(
    '"rows": ', Rows, ']'
    )
| extend Output = replace_string(Output, '\\', '')
| extend Output = replace_string(Output, '}","{', '}, {')

Output:

"Output": "rows": ["{ "timestamp": "2024-01-01T07:51:20.3540000Z", "value": "23" }, { "timestamp": "2024-01-02T07:51:20.3540000Z", "value": "142" }"]]