AWS IoT Rule re-publish topic MQTT

Question

I receive the following input:

$aws/things/siemens-logo/shadow/update
{
  "state": {
    "reported": {
      "I..1:1-1": "01"
    }
  }
}

How can I republish the value as follows:

office/rgb1/light/status
{
   "state": "ON",
}

I'm stuck with the SQL statement.

SQL: SELECT VALUE state from '$aws/things/siemens-logo/shadow/update'

*I found the following page: https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-json.html

Answer 1

SELECT    CASE get(state.reported,"I..1:1-1") 
            WHEN '01' 
            THEN 'ON' 
            ELSE 'OFF' 
          END AS state 
FROM      '$aws/things/siemens-logo/shadow/update'