Mysql dynamic query from key/value object

Hi to all, i'm newbie on node red and JS and i'm writing to you for some help with node red and mysql query.
it's 2 days i'm googling but i can't find solution.
My problem is to built query string dynamically from many nodes or key/value object from node-join result.
I want to do this beacuse i need to write data in to mysqlDB many times in different tables and i would to use one function to do this work without write every time a new function to create query string.

I have many inputs (from text, number, date, switch etc) some times 8 some times 12 some times 20 input nodes.
every one is set with msg.topic like column name in the db and with the join node I create an object with key/value pairs
i.e. {"temp":26.5,"humid":75,"text":"prova","boolean":true,"count":6}
i want to use this to built a INSERT query to write in the corresponding table db like:
INSERT INTO tablename (temp, humid, text, bool, count) VALUE ("....","....",......

some help?

many thanks

Hi, here is an example of constructing a query dynamically using key values as table coulmns and values

[{"id":"87615973.029f1","type":"inject","z":"5a245aa1.510164","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"temp\":26.5,\"humid\":75,\"text\":\"prova\",\"boolean\":true,\"count\":6}","payloadType":"json","x":200,"y":2680,"wires":[["76ca4f2e.3cb2d8"]]},{"id":"76ca4f2e.3cb2d8","type":"function","z":"5a245aa1.510164","name":"","func":"let columns = Object.keys(msg.payload);\nlet values = Object.values(msg.payload);\ncolumns = columns.join(\", \");\nvalues = \"'\" + values.join(\"', '\") + \"'\";\nmsg.payload = `INSERT INTO tablename ( ${columns}) VALUE (${values});`;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":2700,"wires":[["b845cf97.f22d68"]]},{"id":"b845cf97.f22d68","type":"debug","z":"5a245aa1.510164","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":660,"y":2720,"wires":[]}]
let columns = Object.keys(msg.payload);  // create array of column names
let values = Object.values(msg.payload); //create array of values
columns = columns.join(", "); //join coulmns into string 
values = "'" + values.join("', '") + "'"; //join values to string
msg.payload = `INSERT INTO tablename ( ${columns}) VALUE (${values});`; //create query
return msg;
1 Like

Great, really thanks it does working fine for my need.
2 days google for me, 5 minute for you :joy: :+1: :+1: :+1:

1 Like

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