Filter on Azure stream analytics using MetaDataPropertyValue

Question

SELECT  
    GetMetadataPropertyValue("deviceinputstream", '[User].[productFilter]') AS prodFilter
INTO 
    "outputstream" 
FROM 
    "inputstream"

I'm trying to get meta data property values to filter the data. Using the query defined above I'm able to get the meta data property but how do I filter the input stream based on this?

"input stream" is an iot-hub and gets data from a number of devices. This meta data property is what distinguishes different products. What should I write in the where clause?

I tried using WHERE prodfilter = 'product1'` but it didn't work.

Answer 1

If you use prodFilter like this:

SELECT  
    GetMetadataPropertyValue("deviceinputstream", '[User].[productFilter]') AS prodFilter
INTO outputstream
FROM inputstream
WHERE prodFilter = 'product1'

, then the prodFilter in WHERE is not yet defined. You should either use the original expression, like:

SELECT  
    GetMetadataPropertyValue("deviceinputstream", '[User].[productFilter]') AS prodFilter
INTO outputstream 
FROM inputstream
WHERE
    GetMetadataPropertyValue("deviceinputstream", '[User].[productFilter]') = 'product1'

or better yet filter in different step:

WITH step1 AS (
    SELECT  
        GetMetadataPropertyValue("deviceinputstream", '[User].[productFilter]') AS prodFilter
    FROM inputstream

)

SELECT *
INTO outputstream
FROM step1
WHERE prodFilter = 'product1'