Azure Stream Analytics Query showing how we can filter data based on arrays in reference data.

asa-query.sql

SELECT
    RD.DispenserEventId AS EventId, 
    RD.DispenserNumber, 
    RD.LocationId, 
    RD.EventTimeUTC AS Timestamp
INTO
    ehpartnerdata
FROM
    ehrawdispenserfeed RD
JOIN siteandispenserlist SDL ON RD.LocationId = SDL.LocationId
CROSS APPLY GetArrayElements(SDL.PartnerDispensers) As SiteDispenser
WHERE
    RD.EventType = 2
    AND
    SiteDispenser.ArrayValue = RD.DispenserNumber