# Mysql dynamic query from key/value object

**URL:** <https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559>\
**Category:** General\
**Created:** [22 April 2021 16:26 UTC](https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559 "2021-04-22T16:26:54Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![danipenn](https://avatars.discourse-cdn.com/v4/letter/d/c37758/32.png) [@danipenn](https://discourse.nodered.org/u/danipenn)\
**Post date:** [22 April 2021 16:26 UTC](https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559/1 "2021-04-22T16:26:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 April 2021 17:01 UTC](https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559/2 "2021-04-22T17:01:40Z")

</div>

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

```auto
[{"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":[]}]

```

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![danipenn](https://avatars.discourse-cdn.com/v4/letter/d/c37758/32.png) [@danipenn](https://discourse.nodered.org/u/danipenn)\
**Post date:** [22 April 2021 17:14 UTC](https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559/3 "2021-04-22T17:14:00Z")

</div>

Great, really thanks it does working fine for my need.  
2 days google for me, 5 minute for you 😂 👍 👍 👍

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 May 2021 17:14 UTC](https://discourse.nodered.org/t/mysql-dynamic-query-from-key-value-object/44559/4 "2021-05-06T17:14:01Z")

</div>

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