AWS IoT Rule SQL republish get thing name as JSON object key

Question

I'm publishing Shadow updates from multiple iot devices. I want to filter those updates and extract only some data that will update another thing shadow (let's call it main shadow) with a list of Json objects. Each json object key is the thing id.

For that, I have a rule that is subscribed to $aws/things/+/shadow/update/documents to capture all the devices shadow updates. So for example for the following updates

to $aws/things/device101/shadow/update/documents

{
 state: {
    reported: {
      "temperature": "28"
    }
 }
}

to $aws/things/device202/shadow/update/documents

{
 state: {
    reported: {
      "temperature": "31"
    }
 }

the main shadow will result in

{
 state: {
    reported: {
      "device101": {
         "temperature": "28"
      }
      "device202": {
         "temperature": "31"
      }    
    }
 }

In my SQL I still can't find the way to use the thing id as a key in the json data that will be published to the main shadow.

I'm using topic(3) as the function to get the thing name and Literals to build the json but it keeps telling me that it expects a key. I tried to cast the topic(3) function but same result

SELECT { topic(3): {'temperature': current.state.reported.temperature }} as state.reported FROM '$aws/things/+/shadow/update/documents'

SqlParseException
Expected a key, but got IDENT(topic). Object literals should follow the format {"keyName": any-expression, "anotherKey": any-expression} topic(3): 

Any idea how this can be achieved ?

thanks

Answer 1

As of today, it seems that there is no way to achieve this with just SQL in IoT rules. A lambda must be invoked to transform the payload more details in this post