Hello, I am trying to create an IoT Rule that selects only the fields I need from a specified MQTT topic and republishes the message to another MQTT topic. The SQL query I am using is the following:
SELECT state.debug AS state.debug, state.command AS state.command, version
FROM '$aws/things/my-thing/shadow/update/delta'
WHERE state.debug <> '' OR state.command <> ''
When the selected SQL version is the latest (2016-03-23), the right operand of the WHERE clause is completely ignored and the rule is triggered only if the state.debug property has a value. When I change the SQL version to 2015-10-08, however, the rule works just fine.
It seems that there is no breaking change on the way the two SQL versions handle logical operators, according to the AWS documentation. Therefore, is it possible that there is a bug with 2016-03-23 IoT SQL version or am I missing something else here?
AWS OFFICIALUpdated 4 months ago
Hello Greg, thanks for your reply! Yes, unfortunately I am still stuck on this. Could you please provide the SQL query you tried as well as the message you used it on?
The messages published to the topic I am querying are structured this way: { "state": { "debug": { "remoteLogging": false }, "command": "ls", ... }, "version": ... , "timestamp": ... , "metadata": ... } Should I maybe check in a different way than state.debug <> '' OR state.command <> '' whether either of these 2 properties is not empty? Thanks again!
I realize I misread your SQL statement. Now I can indeed see the behaviour. Regardless, it will be more semantic (and it will work) to use the IsUndefined() function:
WHERE IsUndefined(state.debug) = False OR IsUndefined(state.command) = False. I hope this unblocks you.Hey Greg, thanks for you response. Indeed, isUndefined() seems to be exactly what I need! Out of curiosity, have you managed to identify why the OR operator behaves this way in my initial query?
Hi again. No I didn't. It's odd. I raised a ticket for it internally. Nonetheless, undefined is not necessarily an empty string, so the
IsUndefinedfunction is a better choice.