Using an array with where statement

%3CLINGO-SUB%20id%3D%22lingo-sub-2481402%22%20slang%3D%22en-US%22%3EUsing%20an%20array%20with%20where%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2481402%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20pass%20an%20array%20to%20the%20where%20clause%2C%20please%20see%20example%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20terms%20%3D%20pack_array('12345'%2C%20'ABC123')%3B%3CBR%20%2F%3EADFPipelineRun%3CBR%20%2F%3E%7C%20where%20ResourceId%20has_any%20(terms)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20aware%20I%20can%20use%20where%20statements%20with%20'and'%26nbsp%3B%20'or'%20operators%20with%20multiple%20values%2C%20but%20we%20could%20be%20looking%20at%20100s%20different%20values.%3C%2FP%3E%3CP%3ECan%20someone%20please%20advise%20on%20the%20best%20approach%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2493991%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20an%20array%20with%20where%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493991%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087269%22%20target%3D%22_blank%22%3E%40nicholas888%3C%2FA%3E%26nbsp%3B-%20assuming%20you're%20looking%20to%20match%20full%20strings%2C%20you'll%20want%20to%20use%20the%20%22%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Finoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ein%3C%2FA%3E%22%20operator%20to%20achieve%20this.%20To%20reuse%20your%20example%2C%20your%20query%20becomes%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3Elet%20terms%20%3D%20pack_array('12345'%2C%20'ABC123')%3B%0AADFPipelineRun%0A%7C%20where%20ResourceId%20in%20(terms)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22has_any%22%2C%20as%20you%20have%20it%2C%20would%20be%20more%20the%20equivalent%20of%20Col%20contains%20'term1'%20OR%20Col%20contains%20'term2'%20OR%20%5B...%5D.%20%22In%22%20replaces%20the%20contains%20with%20%3D%3Ds.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494048%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20an%20array%20with%20where%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F68646%22%20target%3D%22_blank%22%3E%40Evgeny%20Ternovsky%3C%2FA%3E%26nbsp%3B-%20we%20tried%20to%20match%20the%20resource%20name%20and%20not%20the%20whole%20string.%3C%2FP%3E%3CP%3Eexample%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22nicholas888_0-1624908591751.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292058iE06ECD67F1EF9E55%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22nicholas888_0-1624908591751.png%22%20alt%3D%22nicholas888_0-1624908591751.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ewe%20need%20a%20match%2039515%20only.%26nbsp%3B%20So%20IN%20will%20not%20work.%3C%2FP%3E%3CP%3EPlease%20advise.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to pass an array to the where clause, please see example below:

 

let terms = pack_array('12345', 'ABC123');
ADFPipelineRun
| where ResourceId has_any (terms)

 

I am aware I can use where statements with 'and'  'or' operators with multiple values, but we could be looking at 100s different values.

Can someone please advise on the best approach?

2 Replies

@nicholas888 - assuming you're looking to match full strings, you'll want to use the "in" operator to achieve this. To reuse your example, your query becomes:

let terms = pack_array('12345', 'ABC123');
ADFPipelineRun
| where ResourceId in (terms)

 

"has_any", as you have it, would be more the equivalent of Col contains 'term1' OR Col contains 'term2' OR [...]. "In" replaces the contains with ==s.

@Evgeny Ternovsky - we tried to match the resource name and not the whole string.

example:

nicholas888_0-1624908591751.png

we need a match 39515 only.  So IN will not work.

Please advise.