Mqtt -> Node-Red -> InfluxDB

[URBANsUNITED]

Hi!

I am trying for hours now to get a solution for my project.
What I want:

Get the data from my tasmota energy logger into influxdb to use Grafana later.
All is set up and working except the new Node-Red part.

This is my debug output from "mqtt in node"

Gas-Wasserzähler/tele/SENSOR : msg.payload : Object

{"Time":"2020-04-24T19:59:42","Gas":{"Count":4624.38},"Wasser":{"Count":771.473},"gas_day":0.87,"gas_month":59.65,"gas_year":1.84,"water_day":0.32,"water_month":12.7,"water_year":0.9}

I tried lots of things to get the data using the function node.
ex.

var msg7 = {};

msg7.payload = [{"gas_day": msg1.payload, "gas_month": msg2.payload, "gas_year": msg3.payload, "water_day": msg4.payload, "water_month": msg5.payload, "water_year": msg6.payload}];

return [msg1,msg2,msg3,msg4,msg5,msg6,msg7];

It's not working. Error: TypeError: Cannot read property 'gas_day' of undefined

My screen shot:

I know, it's a very simple thing, but I don't get it!

Who can help a beginner here please??

Thanks in advance

URBANsUNITED

[Steve-Mcl]

@URBANsUNITED

It's not working. Error: TypeError: Cannot read property 'gas_day' of undefined

where are ms1, msg2 etc coming from? As far as I can see they dont exsit!

---

Read working with messages - its worth the 10 minutes & in particular, how to copy the path of a variable from the debug pane

@URBANsUNITED

This is my debug output from "mqtt in node"

Gas-Wasserzähler/tele/SENSOR : msg.payload : Object

{"Time":"2020-04-24T19:59:42","Gas":{"Count":4624.38},"Wasser":{"Count":771.473},"g

pay attention to this - "msg.payload : Object"
its telling you msg.payload is an Object
therefore you can access msg.payload.this & msg.payload.that
e.g. msg.payload.Time

Again, cant stress enough - read "working with messages" and you'll fix this in no time.

come back if you get stuck

[URBANsUNITED]

Good morning,

I am so sorry, but I don't get it.

Yes, I understand that I can split that message, what I did
ex.

And yes, I know how to get the path to these objects
ex. payload.Gas.Count

But I am not able to pass this info to the influxdb.
It works with the function node?!?
But how?
I got two values into my database, but the wrong ones, wrong naming etc....

Gas Count & gas_month --- Where is the rest????

One of my poor attempts:

var msg = {};

msg.payload = { payload: msg.payload.Time };

return [msg];

I am really no lazy guy, I have HA, homebridge etc. easy fully working. But here I am fully stressed
Can't you give me an example with one value or two values, please?

Should I use influxdb out Node or influxdb batch node?

Thanks again

URBANsUNITED

[Steve-Mcl]

@URBANsUNITED

One of my poor attempts:

var msg = {}; // OVERWRITE msg to a NEW EMPTY object

msg.payload = { payload: msg.payload.Time }; //how can you expect to get msg.payload - you overwrote it above ^

return [msg];

see comments above to understand this problem

Answer...

var t =  msg.payload.Time
var gasCount =  msg.payload.Gas.Count;
msg.payload = { payload: gasCount  };
return msg;

or...

var t =  msg.payload.Time
var gasCount = msg.payload.Gas.Count;
node.send({ topic: "time",  payload: t});
node.send({ topic: "gas/count",  payload: gasCount  });
return null;// already sent

[Steve-Mcl]

@URBANsUNITED

Should I use influxdb out Node or influxdb batch node ?

Sorry, no idea - not using infux myself.

I can assist you with coding errors.

If you provide expected format of the msg (that influx node expects) & the data you wish to send, I can certainly help you get correct format.

[afelix]

@URBANsUNITED

Yes, I understand that I can split that message

Rather than splitting the message, have you thought about accessing the parts directly? Once split, each of those is a different message, and if you insert those parts into influx, they get inserted as different measurements, rather than one single measurement. Again, go back to the link Steve gave you, working with messages. From the original message you get out of MQTT, you have an object with the following payload:

{
  "Time": "2020-04-24T19:59:42",
  "Gas": {
    "Count": 4624.38
  },
  "Wasser": {
    "Count": 771.473
  },
  "gas_day": 0.87,
  "gas_month": 59.65,
  "gas_year": 1.84,
  "water_day": 0.32,
  "water_month": 12.7,
  "water_year": 0.9
}

From a function node directly behind that mqtt-in node, you can access the values as msg.payload.gas_day, msg.payload.gas_month, and so on. To get the object you have described in msg7.payload in your original post, you can try the following:

msg.payload = [
    {
        "gas_day": msg.payload.gas_day, 
        "gas_month": msg.payload.gas_month, 
        "gas_year": msg.payload.gas_year, 
        "water_day": msg.payload.water_day, 
        "water_month": msg.payload.water_month, 
        "water_year": msg.payload.water_year
     }
];
return msg;

What's going wrong in the above image is that once you've split the object, the keys turn into topics, and the payload differs per type. For the Time key, your payload is a string. For Gas, it's an object, gas_year for example, is a number. If you did split it, then pass all those messages to a function node and have it operate on msg.payload.???, the contents of msg.payload, and especially the type of each payload, is different.

Can you explain what the format is that you need?

@Steve-Mcl

see comments above to understand this problem

Answer...

var t =  msg.payload.Time
var gasCount =  msg.payload.Gas.Count;
msg.payload = { payload: gasCount  };
return msg;

or...

var t =  msg.payload.Time
var gasCount = msg.payload.Gas.Count;
node.send({ topic: "time",  payload: t});
node.send({ topic: "gas/count",  payload: gasCount  });
return null;// already sent

What is for your use case the format of your messages that you need? See the influx node's builtin documentation for a description, and work it out for your use case. If you pass us back that information, we can give more detailed information on how to continue.

Edit: this topic is worth a read too, and might allow you to figure it out as well: Help needed with MQTT to influx

[URBANsUNITED]

@afelix

msg.payload = [ { "gas_day": msg.payload.gas_day, "gas_month": msg.payload.gas_month, "gas_year": msg.payload.gas_year, "water_day": msg.payload.water_day, "water_month": msg.payload.water_month, "water_year": msg.payload.water_year } ]; return msg;

THANKS afelix!!!
That was excactly what I wanted!!!

I knew that it was easy, but so easy... Great
With the function code I was able to get all data into influxDB and than to Grafana.
This is how it looks now.

PERFECT!!!

Also many thanks to @Steve-Mcl for explaining me a different my and pointing in the correct direction.
I will read and learn more the next weeks to make all better and better.

Many thanks again

URBANsUNITED