JSON objects not importing into influxDB

[Jimdowding]

Hi All,

Super new here and new to node red (and influxDB and all coding as well) so sorry if this issue is simple.

firstly I'm using node red and influx DB set up on a raspberry pi zero 2 in Docker containers.

My data is coming from the new Pimoroni indoor, it packages up sensor data and other board data in JSON objects like this.

17/11/2022, 15:31:11node: debug 3
enviro/indoor01 : msg.payload : Object
object
readings: object
pressure: 981.38
temperature: 24.02
voltage: 4.975
color_temperature: 5990
gas_resistance: 88215
aqi: 13.5
humidity: 52.48
luminance: 53
nickname: "indoor01"
model: "indoor"
uid: "e6614c311b37ab37"
timestamp: "2022-11-17T15:30:19Z"

From what I read about influx, it should be able to accept this type of data, but it's not going anywhere.

my flow at the moment looks like this. a simple in and out

apologies if there's anything missing.

Thanks

James

[Colin]

Welcome to the forum @Jimdowding

If you look at the help text you will see that this is not a valid structure for the data. You are trying to save an object called readings to the database, but it can only store simple data, not objects. If you added a Change node to Move msg.payload.readings To msg.payload then it should work. Note though, that if you the data is for the current time then there is no need to save the time, so I suggest removing that (which you can also do in the change node). In addition I suspect that one or more of the string values should go to influx tags rather than fields (so that they are indexed and can be used for selecting data efficiently).

[Jimdowding]

Colin,

Thank you so very much for your quick and knowledgeable response. it appears to have worked!! hurray! my data is getting through to influx DB and now showing some of the data on grafana.

I thought multiple objects were allowed in influx DB because of this

If msg.payload is an array containing two objects, the first object will be written as the set of named fields, the second is the set of named tags.

as for the data. this is being logged every 15 mins on the board and then every 5 logs are going to be uploaded so the time stamp is going to be necessary.

is there a way to include that?

do I need to include some other function.

Thank you again.

James

[Colin]

@Jimdowding

If msg.payload is an array containing two objects, the first object will be written as the set of named fields, the second is the set of named tags.

Possibly that could have been better phrased. It means that the members of the first object are written as the fields of the record, and the members of the second object are written as the tags of the record. Probably that is what you should be doing, with nickname and model as tags. Possibly also the uid, it depends what it is. If it changes each time then it should be a field, if it is fixed with the sensor then probably it should be a tag.

To include the timestamp it should be written a field called time. I think it will cope with an ISO timestamp so it may just be a matter of changing it from timestamp to time.

@Jimdowding

as for the data. this is being logged every 15 mins on the board and then every 5 logs are going to be uploaded

Can't you send it immediately rather than cacheing it and sending it later?

@Jimdowding

is there a way to include that?

The most efficient way to write multiple records is to use the influx batch node, which will do all five at once. However it is a bit fiddly to setup and since you are dealing with such a small amount of data the efficiency is irrelevant, so I suggest carrying on the way you are, just moving the timestamp value to time.

[Jimdowding]

Hi @Colin,

Thanks very much for your response.

And apologies, for some of the info you mention I don't quite understand.

I've moved the reading object to the payload, but the items in the 'outside' object like time, how do I move that to be included within the reading in the payload.

The reason for logged and delayed uploads is because it's a simple board and is being powered by some AAA batteries, so fewer uploads means longer battery life.

Thanks and once again, apologies for not knowing anything.

James

[Colin]

@Jimdowding

I've moved the reading object to the payload, but the items in the 'outside' object like time, how do I move that to be included within the reading in the payload.

You have to be careful not to overwrite the existing data in the payload before you have go all the data. One way is, move the data to a temporary property, msg.data, before overwriting the payload, so in the Change node do something like:

Move msg.payload.readings To msg.data
Set msg.data.time To msg.payload.timestamp
Move msg.data To msg.payload

Feed that into a debug node to make sure it looks right before connection to the database node.

[Jimdowding]

Hurray! Thanks again @Colin

I've now managed to get the timestamp and the device nickname into the payload with the readings.

04/12/2022, 00:15:07node: debug 5
enviro/indoor01 : msg.payload : Object
object
pressure: 1006.46
temperature: 16.43
voltage: 2.581
color_temperature: 0
gas_resistance: 105523
aqi: 14.1
humidity: 62.61
luminance: 0
time: "2022-12-04T00:15:03Z"
nickname: "indoor01"

but as is usually the case with these things (as I've found anyway) when you move past one issue, there's another one waiting for you. This time, when I try and import this data into influx DB there's an error with the timestamp and influx isn't liking the format. I'm not sure what's wrong there, because I thought influx accepted timestamps in this standard timestamp format, from what I've read online anyway. is it that for some reason its thinking this field should be numeric only? This is on a brand new database (or bucket) as well, so I thought it formatted the fields based on their first input?

04/12/2022, 00:15:07node: Influx DB home_sensor (with time)
msg : error
"Error: Expected numeric value for, timestamp, but got '2022-12-04T00:15:03Z'!"

when I import the data without a timestamp this is the format influx creates a timestamp for the records.

I did try a method from a YouTube video where someone had the same issue, they used the following function to convert the time stamp, but I got an error when I tried that.

date = new Date(Date.parse(msg.payload.timestamp));

msg.payload.timestamp = date.ISOString();
return msg;

but it returns an error saying that .ISOString() is not a function.

I now have another indoor sensor, and an outdoor one, so hopefully once I've got this timestamp issue out the way I'll be able to set the others up.

Thanks again for your help.

James

[Colin]

Is the time you are trying to add the current time? If so then you don't need to include it at all, influx will add the current time automatically.

[Jimdowding]

Hi Colin,

No, because the device is battery powered, it's going to be collecting readings once every 15 mins, then after 5 or so collections it will upload the 5 readings to save on power.

Thanks

James

[Colin]

@Jimdowding

time: "2022-12-04T00:15:03Z"

Since you are not using the batch node you have to provide the timestamp in a form that the database is happy with. Using the batch node it is less critical as the node knows that you are providing a timestamp and can convert it appropriately.

So you have to provide the time as a numerical value (as the error says). You have to provide it in whatever units you have specified for Precision in the influx node. If you have specified millisconds then if the date is in an ISO date format string then you can use
const theTime = new Date(your_variable_containing_date_string).getTime()

[Jimdowding]

Hi @Colin

Honestly I can't thank you enough.

I'm now getting multiple uploads into influxDB from two of these boards with the time being from the boards and not the imported time.

all your suggestions have been spot on!

Thanks again for helping and sharing.