IoT Rule SQL with hyphens

Question

Problem: IoT Core SQL throws an error when accessing columns and defining aliases with hyphens. Putting quotes, double quotes and back quotes does not help the case.  
Scenario: I have message format with skeleton-casing and need to put payload into DynamoDB. Using DynamoDBv2.  
Example message:  
{  
    "header":{...}  
    "payload":{  
        "item-id": ...  
        "timestamp": ...  
        "data": ...  
    }  
}  
IoT Core Rule SQL statement:  
SELECT 'payload.item-id' as 'item-id', payload.timestamp as timestamp, payload.data as data  
FROM "my-topic"  
  
Is there a way to keep skeleton-casing and put fields from payload into individual columns in DDB from IoT Core (without using Lambda)?  
  
Edited by: Cell2749 on Apr 8, 2020 3:57 AM  
  
Edited by: Cell2749 on Apr 8, 2020 3:58 AM

Accepted Answer

Hi Cell2749,  
  
Thanks for reaching out. For your question, I would recommend you to use a standard sql alias, such as "item-id" or "id". Also, you can use AWS IoT DynamoDBv2 Action to write all or part of an MQTT message to a DynamoDB table. Each attribute in the payload is written to a separate column in the DynamoDB database. Reference (https://docs.aws.amazon.com/iot/latest/developerguide/iot-rule-actions.html#dynamodb-v2-rule)  
  
I am interested in learning a bit more about your use cases for including hyphen as a requirement as well.  
  
Hope this helps!  
  
Thanks,  
Ningwei

IoT Rule SQL with hyphens

Contenus pertinents