Hi guys. I’m having trouble deploying a AWS IoT rule that contains the SQL function get. The SQL works fine if I create the rule manually through the AWS console, so my guess is that it triggers something in CloudFormation. Any idea what the problem might be? Can I “escape” the function somehow, or disable validation?
sql: “SELECT topic(3) as stateDeviceId, timestamp as stateTime, version as stateVersion, case state.reported.activeUUID = get(state.reported.sensorUIIDs, 0).uuid when true then ‘Closed’ else ‘Open’ end as stateCurrent FROM ‘$aws/things/+/shadow/update/accepted’ WHERE regexp_matches(topic(3), ‘magnetSensor’) = true”
If I remove the get part, or put single quotes around it in the form of ‘get(state.reported.sensorUIIDs, 0).uuid’, it deploys, but obviously putting single quotes around it makes the rule defect.
An error occurred: UpdateComponentStateIotTopicRule1 - Errors encountered while validating query.
ERROR: 1-indexed number is out of bounds: 0.
Any ideas? I’ve spent quite a bit of time trying to figure this out, but am none the wiser.