ADX - KQL: Conditionally Extending Property Columns from Dynamic Column

let systems = datatable(traceName: string, trace: string)
[
    'Temp1', 'trace_1_temperature',
    'Temp2', 'trace_2_temperature',
    'Temp3', 'trace_3_temperature'
];
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
    datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage':5, 'active':'true'})
];

My desired output hardcoded for this case:

let hardCodedDesiredOutput = telemetry
| extend Temp1 = data['trace_1_temperature']
| extend Temp2 = data['trace_2_temperature']
| extend Temp3 = data['trace_3_temperature']
| project-keep timestamp, Temp1, Temp2, Temp3;
hardCodedDesiredOutput

Output:

timestamp                   Temp1   Temp2   Temp3
2023-09-11 10:00:00.0000000 35  40  
2023-09-11 10:05:00.0000000 36      42
2023-09-11 10:10:00.0000000     38  41

As you can see above, we have extracted a subset of properties from the Dynamic data column. Note that the systems table is structured similar to the output, s.t. columns are named based on an associated friendly name. I desire a more dynamic approach to extending the columns without explicit naming.

I've ran into this in the past, where we may have N many dynamic properties per telemetry row entry.I have tried using bag_unpack with outputSchema, however I'm still struggling to reconcile pulling in another table such as Systems to relate the data.

It feels like there should be a more straightforward programmatic way to do this in KQL, perhaps I'm missing something obvious. Is it possible to bag_unpack while filtering out certain properties conditionally, e.g. for each properties, only unpack iff property.key startswith "VAL".

// Desired structure:
// let dynamicExtend = telemetry
// | based on traces listed in systems, extend a column for each listed trace
// Alternatively some sort of extract-from-dynamic based on if the key property startswith/endswith a value e.i. "temperature"

Try with the below approach, it uses mv-expand and make-bag to dynamically retrieves telemetry data from the systems table. The bag_unpack method separates the resulting bag into individual columns.

let systems = datatable(traceName: string, trace: string)
[
    'Temp1', 'trace_1_temperature',
    'Temp2', 'trace_2_temperature',
    'Temp3', 'trace_3_temperature'
];
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
    datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage':5, 'active':'true'})
];

let extractedTelemetry = telemetry
| extend data = todynamic(data)
| mv-expand kvp = bag_keys(data) 
| extend kvp = tostring(kvp)  
| extend value = data[kvp]  
| project timestamp, kvp, value;  

let mappedTelemetry = extractedTelemetry
| join kind=inner (systems) on $left.kvp == $right.trace
| project timestamp, traceName, value;  

let dynamicOutput = mappedTelemetry
| summarize make_bag(pack(traceName, value)) by timestamp
| evaluate bag_unpack(bag_);

dynamicOutput

Output: