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

kunming jian 60 Reputation points
2024-06-13T06:20:28.4866667+00:00

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'
),
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
342 questions
Accepted answer
  1. Sander van de Velde | MVP 30,711 Reputation points MVP
    2024-06-13T18:29:08.5566667+00:00

    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:

    enter image description here

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deepanshukatara-6769 6,965 Reputation points
    2024-06-13T06:31:46.43+00:00

    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