The WHERE clause in Azure Stream Analytics is unable to perform conditional filtering

Question

My metadata is in JSON format, and I'm attempting to utilize the WHERE clause to filter out data where the property name is 'type', but the filter condition is not working effectively. Could you advise on how to achieve this? Looking forward to your solution. Thanks!

Here's my specific code snippet for reference:

EventEachBinary AS(
    SELECT              
        iot.deviceId,
        CAST(iot.recordTime AS datetime) as recordTime,
        recordProperty.PropertyName as cloudName,
        UDF.ConvertToBinaryArray(recordProperty.PropertyValue) as BinaryList
    FROM IotBaseInput as iot
    CROSS APPLY GetArrayElements(iot.data) AS arrayElement
    CROSS APPLY GetRecordProperties(arrayElement.arrayvalue) AS recordProperty
    Where recordProperty.PropertyName != 'type'
),

Answer 1

Hello @kunming jian ,

welcome to this moderated Azure community forum.

I tested the given message format and tried to transform that format in a regular table format:

WITH IotBaseInput AS(
		SELECT
			*
		FROM
		Input1
),

EventEachBinary AS(
		SELECT              
			iot.deviceId,
			CAST(iot.recordTime AS datetime) as recordTime,
		    CAST(GetArrayElement(iot.data, 0).type AS bigint) AS Type,
			CAST(GetArrayElement(iot.data, 0).event1 AS bigint) AS Event1,
			CAST(GetArrayElement(iot.data, 0).event2 AS bigint) AS Event2
		FROM 
		IotBaseInput as iot
)

SELECT
	*
FROM EventEachBinary 

Note: The query can be optimized by I stayed quite close to your sample query.

It gives me:

I used this GetArrayElement because the JSON array has only one element.

It's recommended to test this for yourself using VS Code and the Stream Analytics Extension. There, you can test with a local file as input.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.

Answer 2

Hi @kunming, Welcome to MS Q&A

In your SQL code snippet, you're attempting to filter out data where the property name is 'type' using the WHERE clause. However, it seems that the filter condition is not working effectively. One common reason for this could be the case sensitivity of the comparison.

Here's how you can modify your WHERE clause to perform a case-insensitive comparison:

EventEachBinary AS(
    SELECT              
        iot.deviceId,
        CAST(iot.recordTime AS datetime) as recordTime,
        recordProperty.PropertyName as cloudName,
        UDF.ConvertToBinaryArray(recordProperty.PropertyValue) as BinaryList
    FROM IotBaseInput as iot
    CROSS APPLY GetArrayElements(iot.data) AS arrayElement
    CROSS APPLY GetRecordProperties(arrayElement.arrayvalue) AS recordProperty
    WHERE LOWER(recordProperty.PropertyName) != 'type'
),
Azure Stream Analytics

In this modification, we're converting the recordProperty.PropertyName to lowercase using the LOWER() function before comparing it with 'type'. This ensures that the comparison is case-insensitive, which might help in filtering out the desired data correctly.

If the issue persists even after this modification, ensure that the data actually contains property names with the exact value 'type'. If there are variations in case or additional characters, you might need to adjust the comparison accordingly.

Kindly accept answer , if it helps, if you have any questions kindly check

Thanks

Deepanshu