CODEWITHSUNDEEP
amazon-web-servicesaws-iotaws-iot-coreamazon-timestream
r2rp4l
r2rp4l

Reputation: 21

AWS IoT Core rule sql for dynamic MQTT key/values

I want to use AWS IoT Core as a MQTT broker and write them to an Amazon Timestream database using IoT rules.

The MQTT messages come from different sensors and therefore there are different keys in the messages.

For example I have

{
  "points": {
    "fan_speed": {
      "value": 30
    },
    "humidity": {
      "value": 70
    }
  }
}

and

{
  "points": {
    "co2": {
      "value": 7.3
    }
  }
}

Now I want to write an IoT rule which gets these values as elegant as possible into the Timestream database and looks like this:

measure_name measure_value::double measure_value::bigint measure_value::bigint
fan_speed NULL 30 NULL
humidity NULL NULL 70
co2 7.3 NULL NULL

I could write a SQL in which I query all possible values, but that looks very inelegant. I look for something that gets me dynamically all keys and values in "points", something like SELECT getKeysAndValues(points.*, "value") FROM "mytopic"

It will work when I do something like this

SELECT points.fan_speed.value AS fan_speed, points.humidity.value AS humidity, points.co2.value as co2 FROM "mytopic"

But then I need to know all keys in advance and every time a new device with a new key gets added I need to modify the SQL.

Upvotes: 0

Views: 242

Answers (1)

João Artur Ceresoli
João Artur Ceresoli

Reputation: 11

Can you use a data as:

{
  "points": {
    "fan_speed":30,
    "humidity": 70
   }
}

and use SQL

Select points.*

its use a key name as a measure_name and a key value as a measure_value, how do you want to do.

If you dont modify the payload, you can use nested object queries, but to do this, do you need to have know the keys.

Upvotes: 0

Related Questions