Json to influxdb

[Erik Näsström]

I'm trying to figure out how to parse a json string that's delivered through mqtt to a format that I can store in my influxdb database:

{"Sequence":35851,"Watt": 7372.8,"kWh":18889.219,"battery":100,"FreqErr":0.57}

I've added a Json node and a split node that splits the message at "," so now I'm getting each value alone like this:

I've been trying to figure out if I could use parts from my other function that adds information to my DB from different topics using msg.parts

var tokens = msg.topic.split("/");
var dest = tokens[tokens.length-1];
msg.url ="http://192.168.1.15:8086/write?db=nodered";
msg.payload = tokens[2] + ",measurement=" + tokens[3] + ",device=" + tokens[1] + " value=" + msg.payload;
return msg;

Anyone that can lend a hand or point me in the right direction would be helpful!

[Mark Setrem]

The very top of your screenshot shows that msg.payload is an javascript object.  

You can therefore refer to each value without the json and split nodes.

e.g.  msg.payload.Sequence , msg.payload.Watt etc.

(you can test this clicking the arrow at the beginning of the line in the debug and then when you hover over one of the lines that is displayed more icons will appear at the end of the line.

(for example copy the path - it's relative to what the debug node is displaying so if the debug is displaying msg.payload it won't include the msg part)

Given this you should be able to replace the array references with the right object references in your function

[Erik Näsström]

Sweet, thanks  @Mark for the help with that, never worked with java in node-red before... Got it working now but would like to streamline it some more... right now it looks like this:

msg.url ="http://192.168.1.15:8086/write?db=sparsnas";
msg.payload = "sparsnas" + ",measurement=Watt " + " value=" + msg.payload.Watt;
return msg;

Can I insert the topic to as something, ie the Watt part after measurement= ?

btw, this is how it looks right now, I don't have the "real" mqtt topic so just faking it right now!

[Mark Setrem]

So node-red uses javascript which is a totally different language than java.  If you are starting out theres some useful guides:

http://noderedguide.com - to node-red 

It's also worthwhile working through one of the online tutorials to Javascript arrays and objects  such as https://www.w3schools.com/js/js_arrays.asp

I'm not sure what you mean by "Can I insert the topic to as something, ie the Watt part after measurement= ?"

But if its can you include more than one value ?  Then yes you can concatenate values in javascript  such as:

msg.payload = "Watt"+msg.payload.Watt+" Sequence"+msg.payload.Sequence;

But you would obviously need to get the syntax correct for your db

[Mike Blackstock]

If you'd rather not assemble the HTTP requests yourself, you could try out the influxdb node.  This way you can work with Javascript objects, although you still may need to do some transformations.

https://www.npmjs.com/package/node-red-contrib-influxdb

Let me know if you try it out, and if you have any feedback.

Mike

[Ross]

Check out Object.keys(msg.payload) - will let you iterate through the keys in the payload:

var tokens = msg.topic.split("/");

msg.url ="http://192.168.1.15:8086/write?db=nodered";

// assumes topic is device/name/measurement

var influxRecord = tokens[2] + ",measurement=" + tokens[3] + ",device=" + tokens[1];

var kv = msg.payload;

if (typeof msg.payload == "string") {

    kv = JSON.parse(msg.payload); 

}

var keys = Object.keys(kv);

//add key=value pairs

keys.forEach(function(key) {

    influxRecord += " " + key + "=" + kv[key];

    });

// tack on timestamp

influxRecord += " " + new Date().getTime();

msg.payload = influxRecord;

return msg;

[Julian Knight]

You might also find the JSONata facility of the change node of considerable use ;-)

[steve rickus]

I could not agree more with Julian -- you already have a JSON object with a topic and payload, so you should have all the info you need to build the payload structure that the Influxdb Out node expects...

If I put your raw data into the Jsonata Exerciser (left side) and use the expression (upper right), you can see the output result (lower right) is close to what you need to pass to the influx node:

The exerciser site also provides a way to share this dataset/expression, so you can open the url http://try.jsonata.org/BJFqFuSWG and modify it for yourself! Very handy. The JSONata lambda syntax can be a bit tough to understand at first, but it provides a very compact way to restructure node-red msg objects. It has saved me a LOT of javascript coding.

--

Steve

On Wednesday, December 6, 2017 at 8:00:48 AM UTC-5, Julian Knight wrote:

You might also find the JSONata facility of the change node of considerable use ;-)

On Tuesday, December 5, 2017 at 1:33:58 PM UTC, Erik Näsström wrote:

I'm trying to figure out how to parse a json string that's delivered through mqtt to a format that I can store in my influxdb database:

{"Sequence":35851,"Watt": 7372.8,"kWh":18889.219,"battery":100,"FreqErr":0.57}

I've been trying to figure out if I could use parts from my other function that adds information to my DB from different topics using msg.parts

[Julian Knight]

haha, I wondered if you would pipe up Steve :-)

Just a note to say that the exerciser uses a later version of JSONata to that in Node-RED and Node-RED has some custom variables.

[Ross]

oo - forgot about JSONata - haven't really used it it in anger yet - will definitely try harder!

[Mario Bianchi]

Hi, you can do something similar to this:

temp=msg.payload;
msg.payload={};

msg.payload.measurement=tokens[3];

msg.payload.device=tokens[1];

msg.payload.value=temp;

And so on..... this way you have a JSON instead of a string showing like a JSON