Looping over a tabular expression and run subquery for each row in KUSTO

Question

I have 2 tables (Tab1 , Tab2) with no common field, I would like to run a query on Tab2 for each row in Tab1 and Where condition in subquery should use column value from outer query.

Pseudo code 

tab1
| project timestamp, duration

tab2
| project event_time_t, data

pseudo code/ desired query

tab1 join tab2
| where event_time_t between (tab1.timestamp .. datetime_add('millisecond',toint(tab1.duration),tab1.timestamp))

I have checked below options but couldn't got it working

MV-Apply : couldn't got it working with tabular expression
User defined function : have limitation with changing row context data
repeat : works only with scalar

UPDATE:

Adding more clarity to my question.

I want to find all rows from T2 which lies between T1.timestamp and add_datetime(T1.timestamp + duration_millisecond)

I did tried the cross join approach and got desired result but its to costly as my T2 table is huge. Can i do this programmatically some how as we do in T-SQL corelated subquery where a subquery gets executed for each row in T1 and change the between clause dynamically with reference to outer query?

let T1 = datatable(OperationID:sring,timestamp:datetime, duration_millisecond:int)
[
    1, datetime(2021-02-15T10:56:28.768), 22 ,
    2, datetime(2021-02-15T11:28:28.165), 17 ,
    3, datetime(2021-02-15T12:29:48.954), 90 ,
    4, datetime(2021-02-15T13:30:09.786), 2 ,
    5, datetime(2021-02-15T14:30:22.333), 6 ,
];
let T2 = datatable(event_time_t:datetime, data:string)
[
    datetime(2021-02-15T10:56:28.775), "Some string", 
    datetime(2021-02-15T10:56:28.788), "Another string", 
    datetime(2021-02-15T11:28:28.167), "Yet another string", 
    datetime(2021-02-15T12:29:48.954), "Last string1",
    datetime(2021-02-15T12:29:48.999), "Last string2",
    datetime(2021-02-15T12:29:49.023), "Last string3",
    datetime(2021-02-15T12:29:49.025), "Last string4",
    datetime(2021-02-15T13:30:09.787), "Last string5",
    datetime(2021-02-15T14:30:22.335), "Last string6",
    datetime(2021-02-15T14:30:22.337), "Last string7",
];
T1
| extend temp=1
| join kind=fullouter (
    T2
    | extend temp=1
) on temp
| project-away temp, temp1
| where event_time_t between (timestamp .. datetime_add('millisecond',duration_millisecond,timestamp))

Expected Result:

Result = datatable(OperationID:sring,timestamp:datetime, duration_millisecond:int, event_time_t:datetime, data:string)
[
    1, datetime(2021-02-15T10:56:28.768), 22 ,datetime(2021-02-15T10:56:28.775), "Some string",
    1, datetime(2021-02-15T10:56:28.768), 22 ,datetime(2021-02-15T10:56:28.788), "Another string",
    2, datetime(2021-02-15T11:28:28.165), 17 ,datetime(2021-02-15T11:28:28.167), "Yet another string",
    3, datetime(2021-02-15T12:29:48.954), 90 ,datetime(2021-02-15T12:29:48.954), "Last string1",
    3, datetime(2021-02-15T12:29:48.954), 90 ,datetime(2021-02-15T12:29:48.999), "Last string2",
    3, datetime(2021-02-15T12:29:48.954), 90 ,datetime(2021-02-15T12:29:49.023), "Last string3",
    3, datetime(2021-02-15T12:29:48.954), 90 ,datetime(2021-02-15T12:29:49.025), "Last string4",
    4, datetime(2021-02-15T13:30:09.786), 2 ,datetime(2021-02-15T13:30:09.787), "Last string5",
    5, datetime(2021-02-15T14:30:22.333), 6 ,datetime(2021-02-15T14:30:22.335), "Last string6",
    5, datetime(2021-02-15T14:30:22.333), 6 ,datetime(2021-02-15T14:30:22.337), "Last string7", 
]

Can you provide an example using two datatables and the expected output? — Avnera

Slavik N Slavik N · Accepted Answer · 2021-02-15T12:40:17

You can join two tables that don't have a common column, by adding a temp column to both columns with the same value (e.g. | extend temp=1), and then joining by this new temp column (e.g. | join kind=fullouter (...) on temp).

Here's an example:

let T1 = datatable(timestamp:datetime, duration:timespan)
[
    datetime(2021-02-15T10:56:28), time(00:05:00.0102814),
    datetime(2021-02-15T11:28:29), time(00:07:01.0245718),
    datetime(2021-02-15T12:29:48), time(01:00:00.6943432),
    datetime(2021-02-15T13:30:09), time(00:45:02.3022766),
    datetime(2021-02-15T14:30:22), time(00:10:06.2003221),
];
let T2 = datatable(event_time_t:datetime, data:string)
[
    datetime(2021-02-15T11:22:48.4361218Z), "Some string", 
    datetime(2021-02-15T12:23:13.4319484Z), "Another string", 
    datetime(2021-02-15T13:55:54.8762753Z), "Yet another string", 
    datetime(2021-02-15T14:28:13.5785646Z), "Last string", 
];
T1
| extend temp=1
| join kind=fullouter (
    T2
    | extend temp=1
) on temp
| project-away temp, temp1
| where event_time_t between (timestamp .. duration)

By the way, note that I simplified your between filter so that the right side will contain a timespan, instead of a datetime - it's slightly more efficient, and is easier to read.

Looping over a tabular expression and run subquery for each row in KUSTO

1 Answers