CODEWITHSUNDEEP
azure-stream-analytics
Vort
Vort

Reputation: 13

Ignore data from stream analytics input array containing a certain value

Using this example data:

{
"device":"TestDevice",
"id":30,
"flags":[ "New", "Ignore"]
}

I want to select all data without the flag "Ignore", I got it working with an udf:

SELECT 
 device, id, flags
 FROM input
 WHERE udf.ArrayContains(flags, "Ignore) = 0

Is it possible to do this without an user defined function?

Upvotes: 0

Views: 29

Answers (1)

mauridb
mauridb

Reputation: 1579

This will do the trick

with cte as 
(
    select
        i.*        
    from
        localInput as i
    outer APPLY
        getarrayelements(i.flags) ae
    where   
        ae.ArrayValue != 'Ignore'    
    or 
        getarraylength(i.flags) = 0
)
select
    c.id,    
    c.device,
    c.flags
from    
    cte c
group by  
    c.id,    
    c.device,
    c.flags,
    System.Timestamp
having
    count(*) = getarraylength(c.flags) 
or 
    getarraylength(c.flags) = 0

I tested it with the following sample data:

{"device":"TestDevice1","id":1,"flags":[ "New", "Ignore"]}
{"device":"TestDevice2","id":2,"flags":[ "New"]}
{"device":"TestDevice3","id":3,"flags":[ "Ignore"]}
{"device":"TestDevice2","id":4,"flags":[ "Something", "Else"]}
{"device":"TestDevice2","id":5,"flags":[]}

Upvotes: 1

Related Questions