Replace a value inside a string with comma separated values

General
1

Hello!

I'm working on a proximity detection project. A user is carrying a BLE beacon. Everytime the beacon broadcasts a BLE gateway is sending to a server a MQTT message (plain text) that contains a timestamp, beacon MAC address, gateway MAC address and RSSI value.

I have created a flow with a MQTT in node.
After that there is a function node with the following code:

var output = msg.payload.split(",");
var rssi = parseInt(output[3]);
msg = {payload:rssi};
return msg;

in oder to take the RSSI value from the MQTT message string and make it a number.
Next there is a Kalman Filter node that clears the noise in RSSI values.

debug
debug455×541 17.2 KB

My problem is how to replace the old RSSI value with the new filtered one (after Kalman Filter node) on every incoming string and then save it in a MySQL database.

The insert values into db function is:

var myDataArray     = msg.payload.split(",");

var visitorID      = myDataArray[1];
var cellID         = myDataArray[2];
var rssi           = myDataArray[3];
var timestamp      = myDataArray[5];

msg.topic = "INSERT INTO data (timestamp,rssi,cellID,visitorID) VALUES ('"+ timestamp+"','"+ rssi+"','"+ cellID+"','"+ visitorID+"')";

return msg;

The flow is working fine if I choose to save the original data in MySQL (without filtering).

Any help would be much appreciated.

Regards.

2

Does a flow like this work for you?

Untitled 2
Untitled 21000×120 12.1 KB

The input and output data
Untitled 3

[{"id":"4bacaac4d03cd721","type":"inject","z":"699af894cd907364","name":"test data","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"abc,def,ghi,jkl,mno,pqr","payloadType":"str","x":100,"y":200,"wires":[["e2cbe28dfd8357b1","f74a65cd2ad67353"]]},{"id":"e2cbe28dfd8357b1","type":"split","z":"699af894cd907364","name":"","splt":",","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":230,"y":200,"wires":[["119d61a3ee76671d"]]},{"id":"a47ccbafe163f3ae","type":"debug","z":"699af894cd907364","name":"Output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":200,"wires":[]},{"id":"119d61a3ee76671d","type":"switch","z":"699af894cd907364","name":"","property":"parts.index","propertyType":"msg","rules":[{"t":"eq","v":"3","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":350,"y":200,"wires":[["3e9e85bb43b5ac6d"],["14dfc6454a89ad85"]]},{"id":"3e9e85bb43b5ac6d","type":"change","z":"699af894cd907364","name":"Simulated filter","rules":[{"t":"set","p":"payload","pt":"msg","to":"XYZ","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":180,"wires":[["14dfc6454a89ad85"]]},{"id":"14dfc6454a89ad85","type":"join","z":"699af894cd907364","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":650,"y":200,"wires":[["a47ccbafe163f3ae"]]},{"id":"f74a65cd2ad67353","type":"debug","z":"699af894cd907364","name":"Input","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":230,"y":240,"wires":[]}]
3

You could move the original array to another property, as long as the kalman filter node does not overwrite the original message properties it should be available after to recreate.

var output = msg.payload.split(",");
var rssi = parseInt(output[3]);
msg = {payload:rssi,original_payload:output};
return msg;

And after the filter node

var myDataArray     = msg.original_payload;

var visitorID      = myDataArray[1];
var cellID         = myDataArray[2];
var rssi           = msg.payload;
var timestamp      = myDataArray[5];

msg.topic = "INSERT INTO data (timestamp,rssi,cellID,visitorID) VALUES ('"+ timestamp+"','"+ rssi+"','"+ cellID+"','"+ visitorID+"')";

msg.payload = "done"
return msg;

example flow

[{"id":"34f8e0f3f786dbef","type":"inject","z":"452103ea51141731","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"blah,12,34,56,78,90","payloadType":"str","x":190,"y":4300,"wires":[["b47bd49bf532ed30"]]},{"id":"b47bd49bf532ed30","type":"function","z":"452103ea51141731","name":"extract data an move original payload","func":"var output = msg.payload.split(\",\");\nvar rssi = parseInt(output[3]);\nmsg = {payload:rssi,original_payload:output};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":4300,"wires":[["9d6da9252ff78ed7"]]},{"id":"9d6da9252ff78ed7","type":"function","z":"452103ea51141731","name":"simulate kalman","func":"msg.payload+=10;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":4360,"wires":[["599d3eea646303c2"]]},{"id":"599d3eea646303c2","type":"function","z":"452103ea51141731","name":"create query","func":"var myDataArray     = msg.original_payload;\n\nvar visitorID      = myDataArray[1];\nvar cellID         = myDataArray[2];\nvar rssi           = msg.payload;\nvar timestamp      = myDataArray[5];\n\nmsg.topic = \"INSERT INTO data (timestamp,rssi,cellID,visitorID) VALUES ('\"+ timestamp+\"','\"+ rssi+\"','\"+ cellID+\"','\"+ visitorID+\"')\";\n\nmsg.payload = \"done\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":190,"y":4420,"wires":[["066acd9c94c6a7e8"]]},{"id":"066acd9c94c6a7e8","type":"debug","z":"452103ea51141731","name":"debug 112","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":390,"y":4420,"wires":[]}]
4

Thank you for your replies.

I have tested both solutions, and both of them were working perfectly!

I think that I will use the solution from E1cid, because it is closer to what I have already created.

The only change will be

var rssi = parseInt(msg.payload);

instead of

var rssi = msg.payload;

in function that inserts into database, because I prefer an integer instead of a large decimal.

Keep up the good work!

Best regards.

5

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.