AWS IoT Rule - DynamoDB Data Insert

Question

SQL is set in AWS IoT Rule, and data is being inserted into DynamoDB.
SQL : SELECT test1, test2, test3 FROM ‘test/topic'

Publish Topic, Payload as below in MQTT Client.
Topic : test/topic 
Payload : {"test1" : "aa", "test2" : "bb", "test3" : "cc"}

Json View
{
  "id": {
    "S": "bsjdfkqnkasd"
  },
  "createDt": {
    "S": "2023-05-04T10:34:46.579565475"
  },
  "data1": {
    "S": "a"
  },
  "data2": {
    "S": "b"
  }
}

However, it is inserted into DynamoDB as shown below.

Json View
{
  "id": {
    "S": "bsjdfkqnkasd"
  },
  "payload": {
    "M": {
      "test1": {
        "S": "aa"
      },
      "test2": {
        "S": "bb"
      },
      "test3": {
        "S": "cc"
      }
    }
  }
}

How can I store data in DynamoDB as expected?
Thank you.

Answer

To store each attribute individually, you will need to modify the SQL in your IoT rule to handle each attribute separately.

In your IoT rule, set the SQL statement to the following:

```
SELECT payload.test1 as test1, payload.test2 as test2, payload.test3 as test3 FROM 'test/topic'
```

This statement extracts each attribute individually from the payload.

In the action for the rule, select 'Split message into multiple columns of a DynamoDB table (DynamoDBv2)'.

In the 'Configure action' step, select your DynamoDB table and set the Hash key value to your desired key (for example, id).

In the 'Table columns' section, map the incoming payload data to the DynamoDB columns. Use test1, test2, and test3 as the column names and ${test1}, ${test2}, and ${test3} as the values, respectively.

By using this method, the payload from the MQTT message will be split into different attributes in DynamoDB rather than being stored as a map under the payload attribute. Please note that you need to ensure that your DynamoDB table has the appropriate columns (test1, test2, test3) set up for this to work.

AWS IoT Rule -> DynamoDB Data Insert

Relevant content