AWS IoT Core rule sql for dynamic MQTT key/values

Question

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.

João Artur Ceresoli · Accepted Answer · 2023-07-19 14:40:57Z

0

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.

answered Jul 19, 2023 at 14:40

João Artur Ceresoli

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

r2rp4l Over a year ago

Thanks for the answer. Goes in the right direction. Unfortunately we can't change the MQTT schema. Is there a function in the rules SQL to migrate to the schema you mentioned? Like with the transform function?

Collectives™ on Stack Overflow

AWS IoT Core rule sql for dynamic MQTT key/values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related