Good day,

Suppose I have the following data, which tracks the inside and outside temperatures at a certain location. To save on size, it only measures the temperatures every 15 minutes, and then sends all values after an hour.

datatable(T:dynamic)
[
    dynamic
    (
        {
            "name": "Temperature",
            "startTimestamp": "2022-12-14T08:00:00.0000000Z",
            "signals": [
                {
                    "name": "Temperature_inside",
                    "values": [
                        "17.1",
                        "17.5",
                        "17.8",
                        "17.9"
                    ]
                },
                {
                    "name": "Temperature_outside",
                                "values": [
                        "1.3",
                        "1.4",
                        "1.4",
                        "1.5"
                    ]
                }
            ]
        }
    )
]

I've already extended the startTimestamp to a number with | extend DateTimeKey = format_datetime(creationTimestamp, 'yyyyMMddhhmmss'), and I'm currently able to take individual values using:

T
| mv-apply signal = T.signals on
    (
    summarize make_bag(bag_pack(tostring(signal.name), signal.values[0]))
    ) 
| project-away T
| evaluate bag_unpack(bag_)

to get:

, but I would like to get all values in one go, without having to run the query 4 times for every hour.

Now I know there's a range function in Kusto, but I can't get it to work like it would in something like Python. Is there a way to loop over ... , signal.values[i]))? While at the same time updating the DateTimeKey to it's correct value with something like | extend DateTimeKey = DateTimeKey (1500 * i)?

Ideally, the end result would look like:

Thanks in advance!

CodePudding user response：

datatable(T:dynamic)
[
    dynamic
    (
        {
            "name": "Temperature",
            "startTimestamp": "2022-12-14T08:00:00.0000000Z",
            "signals": [
                {
                    "name": "Temperature_inside",
                    "values": [
                        "17.1",
                        "17.5",
                        "17.8",
                        "17.9"
                    ]
                },
                {
                    "name": "Temperature_outside",
                                "values": [
                        "1.3",
                        "1.4",
                        "1.4",
                        "1.5"
                    ]
                }
            ]
        }
    )
]
| mv-expand with_itemindex=i    Temperature_inside  = T.signals[0].values to typeof(decimal)
                               ,Temperature_outside = T.signals[1].values to typeof(decimal) 
| project DateTimeKey = format_datetime(todatetime(T.startTimestamp)   i * 15m, 'yyyyMMddhhmmss')
         ,Temperature_inside
         ,Temperature_outside

Fiddle