republishing a message MQTT AWS

Question

I have a message coming from a topic through MQTT.

I need change the name os the columns of the message.

The original message:

  {
  "timestamp": 1645722065088,
  "Heart Rate Measurement": 24550,
  "Energy Expended": 1900,
  "RR-Interval": 1
   }

I need to take just timestamp and Heart Rate inside of a rule:

SELECT "Heart Rate Measurement"as heartrate, timestamp as date FROM 
'pulsewave/heart_rate'

The timestamp is easy to get but the "Heart Rate Measurement" is not

I ended up getting the following:

{
"heartrate": "Heart Rate Measurement",
"date": 1645722065088
}

any tips to get the message inside of the Heart Rate Measurement? When i set without the quotes it doesnt accept

Answer 1

The rule works for the timestamp attribute but not Heart Rate Measurement as the AWS IoT SQL syntax doesn't support spaces in attribute names.

From https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-reference.html

Attribute names with spaces in them can't be used as field names in the SQL statement. While the incoming payload can have attribute names with spaces in them, such names can't be used in the SQL statement. They will, however, be passed through to the outgoing payload if you use a wildcard (*) field name specification.

An alternate approach is to implement a lambda that projects your JSON payload to an equivalent without the spaces.