Querying bridged MQTT passing into DynamoDB

Question

I can't get a rule working, for MQTT messages being passed into IOT.  
  
In the Test rule I'm subscribing to "mygateway2-out/_/_/_/_/+" and which gets the following JSON:  
{  
  "topicFilter": "mygateway2-out/_/_/_/_/+",  
  "qos": 0,  
  "messages": \[  
    {  
      "format": "json",  
      "topic": "mygateway2-out/61/1/1/0/0",  
      "timestamp": 1621297559099,  
      "payload": 14.9  
    }  
  ]  
}  
  
I have a DynamoDB table with 'sample_time' and 'value' and am trying to populate it with a Rule that looks like this:  
  
SELECT messages,  
  (SELECT VALUE payload FROM messages) as value,   
FROM 'mygateway2-out/_/_/_/_/+'  
  
I'm not getting anything populated in the table.  I've also tried:  
SELECT payload FROM 'messages' as value  
and  
SELECT payload FROM messages as value  
and  
SELECT messages, (SELECT VALUE payload FROM messages) as value  
and  
SELECT VALUE payload FROM messages as value

None of which is inserting any rows in my table.

Answer

The problem I had was the bridged MQTT was not sending the 'payload' in json format.  
{  
      "format": "json",  
      "topic": "mygateway2-out/20/0/1/0/0",  
      "timestamp": 1621717535170,  
      "payload": 56  
}  
  
I would not have had this problem if the "payload" was sent like this:  
...  
      "payload": {  
        "temperature": 28  
}

So I needed to build the json sql query to convert the payload into a numerical value:  
SELECT cast(concat(substring(decode(encode(**, 'base64'),'base64'),0, 2), ".", substring(decode(encode(**, 'base64'),'base64'),3, 8)) as Decimal) AS value FROM 'mygateway2-out/_/_/1/0/0'  
  
Now the numerical values get inserted as "NUM" (numbers) in my DynamoDB table.

Querying bridged MQTT passing into DynamoDB

Contenido relevante