How to extract multiple values from an array into different rows in KQL?

Question

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:

DateTimeKey	Temperature_inside	Temperature_outside
20221214080000	17.1	1.3

, 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:

DateTimeKey	Temperature_inside	Temperature_outside
20221214080000	17.1	1.3
20221214081500	17.5	1.4
20221214083000	17.8	1.4
20221214084500	17.9	1.5

Thanks in advance!

Why the temperature values are strings and not numeric? Why is signals array and not a dictionary? Why do you provide a sample data where the column name is the same as the table name? — David דודו Markovitz
– David דודו Markovitz, Commented Dec 14, 2022 at 8:15
Yeah in hindsight not the smartest, but I couldn't find what I wanted to know anywhere, so I just made up a quick example with some copy-pasting without thinking much about the datatypes and such... — EindbaasJohn
– EindbaasJohn, Commented Dec 14, 2022 at 10:42

David דודו Markovitz · Accepted Answer · 2022-12-14 08:16:30Z

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

DateTimeKey	Temperature_inside	Temperature_outside
20221214080000	17.1	1.3
20221214081500	17.5	1.4
20221214083000	17.8	1.4
20221214084500	17.9	1.5

Fiddle

Collectives™ on Stack Overflow

How to extract multiple values from an array into different rows in KQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related