Re: Split "object" payload to insert into mysql database. Easy Way??

[John O Connor]

On Wednesday, April 6, 2016 at 1:00:56 PM UTC+1, John O Connor wrote:

 Hi Folks,

I need to split this msg payload and then insert it into a mysql database, I can do this writing alot of node functions, but just wondering is there an easier way of doing it!!!

Thanks in advance.

Here is the payload example;

[Mark Setrem]

I assume you mean because the mote number changes?

As it exists in the msg.topic you can extract the mote number from there and then use it to extract the other values 

such as:

msg.payload ={"d":{"001D":{"val":{"id":128}}}};

msg.topic ="/sm/mote/001D/hr/dev";

var patt =/\/sm\/mote\/(\S+)\/hr\/dev/;

var newstr = msg.topic.split(patt);

msg.payload = msg.payload.d[newstr[1]].val.id;

return msg;

[John O Connor]

Yes, because the mote number changes a few times, I need to be able to breakdown the message for Mysql, without writing a seperate function for each.

[Nicholas O'Leary]

What does the sql table look like? Is the intent to insert each of those readings as a single row in the table? Need some sense of how you want it splitting up.

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
For more options, visit https://groups.google.com/d/optout.

[John O Connor]

I originally designed 12 different tables for each mote/mac, but I couldnt compare,
but now, Just bring them all into one table...

So

Mote - and then the various values...charge, battery, voltage, etc..

If you have any input, I am open to ideas, because I think at this stage I have confused myself with the data coming in!!!

John

You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LCjxOWbjJgg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

[Mark Setrem]

So if you follow the few lines in my reply you should be able to
break out the same details irrespective of mote ID. So just have one function and snaffle the data into your database

[Mark Setrem]

So in your screen dump,

The msg.topic is in the form of "/sm/mote/IDNUMBER/hr/dev"

so you can extract the IDNUMBER in the first two lines ( it get stored in the newstr array as item 1 ( where item 0 is the original ) 

var patt =/\/sm\/mote\/(\S+)\/hr\/dev/;

var newstr = msg.topic.split(patt);

You can then use this to breakdown the message into any of the subsequent items irrespective of what the IDNUMBER e.g. :

var id = msg.payload.d[newstr[1]].val.id;

var charge = msg.payload.d[newstr[1]].val.charge;

etc...

And now you have the data as variables you can then reformat then and put it into whatever format you want to for 

[John O Connor]

Thanks Mark for your help on this.

Debug is giving me an error on your function for some reason.

John

[Mark Setrem]

OK i think its because your initial screendump doesn't have a leading / in  the msg.topic/

you could test this by putting node.warn statements in the code that get published to the console.  

but if that is the case try changing the var part line to:

var patt =/sm\/mote\/(\S+)\/hr\/dev/;

so heres a quick mockup that works for me...

[{"id":"101d48c2.efe2b7","type":"function","z":"132fae26.ecd052","name":"","func":"msg.payload ={\"d\":{\"001D\":{\"val\":{\"id\":128}}}};\nmsg.topic =\"sm/mote/001D/hr/dev\";\n\nvar patt =/sm\\/mote\\/(\\S+)\\/hr\\/dev/;\nvar newstr = msg.topic.split(patt);\n\nmsg.payload = msg.payload.d[newstr[1]].val.id;\n\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":320,"wires":[["bb5dc79d.44a238"]]},{"id":"bad68e72.45297","type":"inject","z":"132fae26.ecd052","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":116,"y":166,"wires":[["101d48c2.efe2b7"]]},{"id":"bb5dc79d.44a238","type":"debug","z":"132fae26.ecd052","name":"","active":true,"console":"false","complete":"false","x":428,"y":253,"wires":[]}]

Its often easier to help if rather than giving a screen dump of the error message/data you copy and paste an example of the data or that piece the code.