union with all fields

I try to do multiple queries at once with different aggregation function and thus different result fields.

I would like to use union to add those results together but can't get it to work.

This is what i tried:

Values
| where ts between(datetime(2024-01-01T08:30:32.406Z) .. datetime(2024-09-30T23:45:32.406Z)) and id in ('123') and key in ('1', '2')
| summarize avg(val) by id, key, bin(ts, 1min)
| project id, key, ts, avg_val;
union 
Values
| where ts between(datetime(2024-01-01T08:30:32.406Z) .. datetime(2024-09-30T23:45:32.406Z)) and id in ('123') and key in ('1', '2')
| summarize min(val) by id, key, bin(ts, 1min)
| project id, key, ts, min_val

but this removes the avg_val field from the result.

I also tried defining both queries via let, adding a metric field and then use union on that (like in the answer by @RithwikBojja):

let min_data = Values
| where ts between(datetime(2024-10-08T10:00:00.406Z) .. datetime(2024-10-08T10:10:00.406Z)) 
| summarize min(val) by id, key, bin(ts, 1min)
| project id, key, ts, value = min_val, metric = "min"
| top 200 by ts;

let avg_data = Values
| where ts between(datetime(2024-10-08T10:00:00.406Z) .. datetime(2024-10-08T10:10:00.406Z)) 
| summarize avg(val) by id, key, bin(ts, 1min)
| project id, key, ts, value = avg_val, metric = "avg"
| top 200 by ts;

union avg_data, min_data
| order by id, key, ts

it throws the error: 'union' operator: Failed to resolve table expression named 'avg_data'

How can i have both/all fields in the result?

If i add avg_val to the second queries project line it throws an error 'project' operator: Failed to resolve scalar expression named 'avg_val'

Solution

You can put your queries in brackets and seperate them with a comma.

i.e. union (<query1>), (<query1>)

You can use below KQL Query to get the result:

let rith = datatable(id: string, rith_key: string, timestamp: datetime, val: real)
[
    "008", "1", datetime(2017-09-07T08:30:32.406Z), 100,
    "008", "1", datetime(2017-09-07T08:31:32.406Z), 80,
    "008", "2", datetime(2017-09-07T08:32:32.406Z), 70,
    "008", "2", datetime(2017-09-07T08:33:32.406Z), 30,
    "008", "1", datetime(2017-09-07T08:34:32.406Z), 60,
    "008", "2", datetime(2017-09-07T08:35:32.406Z), 95
];

union (rith
| where timestamp between(datetime(2017-09-07T00:00:00.000Z) .. datetime(2017-09-07T23:59:59.999Z))
      and id in ('008') and rith_key in ('1', '2')
| summarize rith_avg_val = avg(val) by id, rith_key, bin(timestamp, 1min)
| project id, rith_key, timestamp, rith_avg_val),
(rith
| where timestamp between(datetime(2017-09-07T00:00:00.000Z) .. datetime(2017-09-07T23:59:59.999Z))
      and id in ('008') and rith_key in ('1', '2')
| summarize rith_min_val = min(val) by id, rith_key, bin(timestamp, 1min)
| project id, rith_key, timestamp, rith_min_val)
| order by id, key, ts

You can remove unwanted rows by using project-away.

Also check this fiddle for complete query.