AWS IoT DynamoDB rule not able to read value SELECTed from topic() function

Question

I'm trying to get data from an AWS IoT MQTT topic into DynamoDB using a rule. An example topic is cooler/cooler42/sensors and example message

{ "waterTemp": 10, "timestamp": 1580370731383 }

I've defined the query like so, to extract the deviceName (e.g. cooler42) from the topic and insert it into the JSON:

SELECT *, topic(2) AS deviceName FROM 'cooler/+/sensors'

This does indeed seem to work, as if I republish the message to another topic I now see the same JSON with deviceName added:

{ "waterTemp": 10, "timestamp": 1580370731383, "deviceName": "cooler42" }

My understanding is that all 3 fields should now be available for use within my DynamoDB rule like so:

However I can see from CloudWatch that the rule is failing with error One or more parameter values were invalid: An AttributeValue may not contain an empty string (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException and the partition key (aka hash key) is coming through as empty:

{ "ItemRangeKeyValue":"1580370731383", "IsPayloadJSON":"true", "ItemHashKeyField":"deviceName", "Operation":"Insert", "ItemRangeKeyField":"timestamp", "Table":"SensorDataTest2", "ItemHashKeyValue":"" <--- Empty }

Am I not able to use the deviceName I've just SELECTed from the topic name in the rule? If not is there another way to extract it?

NB If I manually publish a message onto the topic already including the deviceName then it does work fine, but I'm working in a constrained environment and don't want the extra payload size.

Ben T Ben T · Accepted Answer · 2020-02-06T00:11:33

The key values use a substitution template which cannot reference the deviceName alias (*).

The AWS documentation emphasises this:

Because an expression in a substitution template is evaluated separately from the "SELECT ..." statement, you cannot reference an alias created using the AS clause. You can reference only information present in the original payload, in addition to supported functions and operators.

You can only reference information in the original payload.

As the alias is not present in the original payload, you can use ${topic(2)} for the partition key value. The function has access to the topic though it means that the key value is coupled to the topic name.

Another alternative is to have the rule republish to another topic with deviceName in the payload. The rule/action that handles this topic can write to DynamoDB. This action will then have access to the deviceName property as it is available in the republished payload.

* The documentation alludes to the key value syntax being substitution templates.

The link above states:

Substitution templates appear in the action parameters within a rule

However, I have also verified that the payload available to functions does not include aliases by using the encode function to write the entire payload that is available in the action to the table.

e.g. with a rule that includes some aliases:

Configure the action to use a Sort key value of ${encode(*, 'base64')} that will encode the entire payload as a base 64 string.

When you publish a message:

This message is written to DynamoDB as:

{
  "DateTime": "ewogICJtZXNzYWdlIjogIkhlbGxvIGZyb20gQVdTIElvVCBjb25zb2xlIgp9",
  "Payload": {
    "arrivalTime": 1581082253585,
    "myField": "Hello from AWS IoT console",
    "payload": {
      "message": "Hello from AWS IoT console"
    }
  },
  "Topic": "blt/test"
}

The entire payload including aliases is in the Payload field. But decoding the base 64 string in the DateTime field will reveal that the aliases are not present to the encode function.

AWS IoT DynamoDB rule not able to read value SELECTed from topic() function

2 Answers