CODEWITHSUNDEEP
azure-data-explorer
dedotatedwam
dedotatedwam

Reputation: 78

Kusto complex json with array

This is my source format:

{
    "message":[
        {"name":"sensorID","value":"5"},
        {"name":"eventT","value":"2021-04-16T19:11:26.149Z"},
        {"name":"pressure","value":"150"}
    ]
}

Looking to flatten it out into a table:

sensorID eventT pressure
5 "2021-04-16T19:11:26.149Z" 150

Cannot for the life of me figure it out.

Splitting the array just gets me a more nested array:

test
| project ray=array_split(message, 1)

And using mv-expand gets me two separate rows:

test
| mv-expand message

At my wits end. any help greatly appreciated.

Upvotes: 0

Views: 624

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

if the schema is unknown in advance, you could try something like this (using mv-apply, summarize make_bag() and bag_unpack())

datatable(d:dynamic)
[
    dynamic({
    "message":[
        {"name":"sensorID","value":"5"},
        {"name":"eventT","value":"2021-04-16T19:11:26.149Z"},
        {"name":"pressure","value":"150"}
    ]}),
        dynamic({
    "message":[
        {"name":"sensorID","value":"55"},
        {"name":"eventT","value":"2021-03-16T19:11:26.149Z"},
        {"name":"pressure","value":"1515"}
    ]})
]
| mv-apply d.message on (
    summarize b = make_bag(pack(tostring(d_message.name), d_message.value))
)
| project b
| evaluate bag_unpack(b)
eventT pressure sensorID
2021-03-16 19:11:26.1490000 1515 55
2021-04-16 19:11:26.1490000 150 5

Upvotes: 2

Related Questions