IOT-Core Rules for Timestream Database with double nested json MQTT Message

Question

I've been trying to import data from an MQTT message into AWS's Timestream database via a rule for some time. I always get the error

"errorMessage": "Failed to write records to Timestream. The error received was 'Errors at dimensions.1: [Errors at dimensions.2: [Dimension value cannot be empty.], All measures invalid. No record written. Errors: The number of valid records per write should be in [1, 100]...'.

The current query:

`SELECT get(payload, 'streams[0].id') as sensorid, ROUND(get(payload, 'streams[0].value')) as sensordata FROM 'topicname'.`

Tried also:

`SELECT get(get(payload, 'streams'), 0) FROM 'topicname'.`

The MQTT Message:

```
{
  "datetime": "2023-06-20T08:49:00Z",
  "payload": {
    "streams": [
      {
        }, "id": { "RS62",
        "value": 885.4801218896597
      },
      {
        }, { "id": "S55",
        "value": 35.89729103822529
      },
      {
        }, { "id": "S56",
        "value": 1.1062225001108246
      },
      {
        }, { "id": "S57",
        "value": 1.9827817232548703
      },
      {
        }, { "id": "S58",
        "value": 1.4765336157706086
      },
      {
        }, { "id": "S59",
        "value": 69.61716498265852
      },
      {
        }, { "id": "S60",
        "value": 75.81964179583608
      },
      {
        }, { "id": "S61",
        "value": 28.12226402898834
      },
      {
        }, { "id": "S62",
        "value": 1491.9799033788302
      }
    ]
  }
}
```

I also tried it with datetime only, which resulted in success. Therefore, it should basically work, but apparently I'm not reaching the two levels below in the nested object.

I found only in the documentation a normal nested object. Not doubled. Every Answer I found, is not my case.

Thanks in advance

Answer

Hi NLVP. There appears to be a formatting error in your MQTT message. I assume the message is meant to start as:

```
{
  "datetime": "2023-06-20T08:49:00Z",
  "payload": {
    "streams": [
      {
        }, { "id": "RS62",
        "value": 885.4801218896597
      },
```

Assuming that, you should have more joy with this:

```
SELECT get(payload.streams, 1).id as sensorid, ROUND(get(payload.streams, 1).value) as sensordata
```

In general, when trying to create more complex rules, I recommend you use the [Republish action](https://docs.aws.amazon.com/iot/latest/developerguide/republish-rule-action.html) to develop and debug the SQL statement.

Answer

Hi Greg,

I have created a rule with the connection to CloudWatch>Log.

`SELECT get(payload.streams, 1).id as sensorid, ROUND(get(payload.streams, 1).value) as sensordata FROM 'device/topic'`

Here I get this return value. It should be correct and write to the timestream database. I don't understand why it says the values are empty. Also, how could I get the entire data. I always find only the "S55". but I think that is a challenge after I have already imported the data into the TimestreamDB ;)

```
@message

{"sensorid":"S55","sensordata":37}

@timestamp

1687337346072

sensordata

37

sensorid

S55
```

I'm pretty much at the end of my thinking. I don't know what the error should be when the values are displayed to me at Cloudwatch>Log.

Republish I haven't figured out yet how it can help me here.

Do you have any other idea maybe?

Best regards

Normen

Answer

Hi Greg,

first of all thank you very much for the answer.

I have tried with the query and unfortunately have not come to any result yet. It still says that the fields are empty.

I will look at the rePublish, thanks for pointing that out.
I have always lacked a direct return of hints as to what the problem may be. A good debugging environment is very valuable for timely error handling :)

Best regards

Normen

IOT-Core Rules for Timestream Database with double nested json MQTT Message

Relevant content