IoT Core Rule SQL Nested Query

Question

Hi,
I have an incoming message to IoT Core as in format:
`{"set":
  {"datapoints":[
    {"name":"abc","type":"int","value":151},
	{"name":"pqr","type":"int","value":246}],
  "timestamp":"2024-03-07T10:42:55.902Z"
  }
}`

i want to transform this incoming message to below format:
`{
    "device":"test",
    "vals": [
        {
            "column_name": "abc",
            "column_type": "Int",
            "timestamp": "2024-03-07T10:07:35.3405950Z",
            "val": "0"
        },
        {
            "name": "pqr",
            "type": "Int",
            "timestamp": "2024-03-07T10:07:35.3406800Z",
            "val": "0"
        }
    ]
}`

The problem is, i am unable to use set.timestamp inside nested query.

here is my Rule SQL:

`                      SELECT 
                        topic(2) AS device,
                        (SELECT "1" AS id,
                          dp.name AS column_name, dp.type AS column_type,
                          0 AS qc,
                          set.timestamp as ts,
                          CAST(dp.value AS String) AS val
                        FROM set.datapoints as dp) AS vals
                      FROM 'measurement/data/+/#'`

Please suggest how i can add timestamp from incoming message to each datapoints objects as attribute.

Answer

Hello,

Please be informed that, AWS IoT Rules Engine does not consists the complete native SQL engine's features. Hence, some queries would not produce the expected results. However the AWS IoT rules provide some great SQL functions as mentioned in the below document for performing various operations.

[+] Functions - aws_lambda(functionArn, inputJson) - https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-func-aws-lambda

Having said, you could try using "aws_lambda" SQL function where the input JSON from the MQTT topic can be passed to the Lambda function by which you could modify the payload as you like and return the modified JSON to AWS IoT and this could be a viable workaround for your use-case mentioned. Please feel free to look into the above link for more details on the same.

IoT Core Rule SQL Nested Query

Relevant content