How do you join two tables based on substring matches in Azure Data Explorer / Kusto?

user_1678772 0
Viewed 708

I would like to create a resulting table from the joining of these two tables based on the cells in the Fruit having substring matches in the Things data table.

let Fruit = datatable(fruit:string) 
[
"apple", 
"banana" 
"orange" 
];
let Things = datatable(fruit:string) 
[
"anappletree",
"myoranges" 
];

I've tried something like this:

Fruit
| join Things on $left.fruit contains $right.thing

But get this error:

Semantic Error 
join: only column entities or equality expressions are allowed in this context.

So can't use contains here.

How do I join this such that I can get a table consisting of

"apple" | "anappletree"
"banana" | ""
"orange" | "myoranges"
kusto-explorer azure-data-explorer
1 Answers

If the left side of the join (in your case - Fruit) is small enough, you could try using mv-apply:

let Fruit = datatable(fruit:string) 
[
    "apple", 
    "banana",
    "orange" 
];
let Things = datatable(thing:string) 
[
    "anappletree",
    "myoranges",
    "this is not a fruit"
];
let Fruit_list = toscalar(Fruit | summarize make_list(fruit));
Things
| mv-apply fruit = Fruit_list on (where thing contains fruit)
thing fruit
anappletree apple
myoranges orange

Alternatively, as join only supports equality as of this writing, you can try using a cross-join, then filtering using contains:

let Fruit = datatable(fruit:string) 
[
    "apple", 
    "banana",
    "orange" 
];
let Things = datatable(thing:string) 
[
    "anappletree",
    "myoranges",
    "this is not a fruit"
];
Fruit
| extend dummy = 1
| join kind=inner (Things | extend dummy = 1) on dummy
| where thing contains fruit
| project-away dummy*
fruit thing
apple anappletree
orange myoranges
user_78618070
Related