# Mysql query with json fields

**URL:** <https://discourse.nodered.org/t/mysql-query-with-json-fields/60256>\
**Category:** General\
**Created:** [23 March 2022 18:35 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256 "2022-03-23T18:35:50Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [23 March 2022 18:35 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/1 "2022-03-23T18:35:50Z")

</div>

Using Node-red I read from MySQL (MariaDB) where one field is in JSON format (so longtext). I use this to build one API get. The query returns an array of strings (for the JSON field) and the http response node return data with backslash (escaped character).

I read some posts about this problem, but I've not found my solution.

The response from MySQL query is like this:

> {"type":[{"int\_status":"battery","unit":"volt" }, "sensor":"soil\_temperature", "unit":"celsius"}, {"sensor":"soil\_moisture","unit":"relative"}, {"sensor":"soil\_conductibility","unit":"uS/cm"}] }

and the http response is this:

> "{"type":[{"int\_status":"battery","unit":"volt" }, {"sensor":"soil\_temperature","unit":"celsius"}, {"sensor":"soil\_moisture","unit":"relative"}, \t{"sensor":"soil\_conductibility","unit":"uS/cm"}] }"

How can do I in order to remove the backslash?

Thanks,  
Fabrizio

---

<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:** [23 March 2022 18:58 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/2 "2022-03-23T18:58:54Z")

</div>

One is an Object the other a String. To change one to the other use a JSON node.

Or set the http request node to output a parse Object.

---

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [23 March 2022 20:53 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/3 "2022-03-23T20:53:16Z")

</div>

> [@E1cid](#):
>
> http request node

I've tried with JSON node, maybe in the wrong mode. I'll look better and I'll check the use of http request node.  
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:** [23 March 2022 21:13 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/4 "2022-03-23T21:13:49Z")

</div>

Missed the `\t` you can remove it with a change node. e.g.

```auto
[{"id":"593b500f.63c1d","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"type\":[{\"int_status\":\"battery\",\"unit\":\"volt\"},{\"sensor\":\"soil_temperature\",\"unit\":\"celsius\"},\\t{\"sensor\":\"soil_moisture\",\"unit\":\"relative\"},{\"sensor\":\"soil_conductibility\",\"unit\":\"uS/cm\"}]}","payloadType":"str","x":140,"y":1420,"wires":[["3b699d12.457062"]]},{"id":"3b699d12.457062","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"\\t","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":1420,"wires":[["fddf6a7a.b13628"]]},{"id":"fddf6a7a.b13628","type":"json","z":"bf9e1e33.030598","name":"","property":"payload","action":"","pretty":true,"x":500,"y":1400,"wires":[["2f3dd1f.61c9aae"]]},{"id":"2f3dd1f.61c9aae","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":680,"y":1400,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [24 March 2022 21:17 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/5 "2022-03-24T21:17:52Z")

</div>

Maybe I understood.  
The MySQL query (MariaDB) returns one array with a number of elements equal to the rows returned from DB.  
Now fields data are interpreted, by http response node, as JSON except for the DB JSON field (in fact a longtext). They are intended as a text string and, characters like ", are escaped by http response node. So the only parts to be converted to JSON seems to be these fields.

Not all is clear to me. Anyway, in this mode, it works.  
In my application, the JSON DB field is named d\_type and this is the function appplied:

```auto
var str;
var pl = msg.payload;
// just d_type is text
for (var i = 0; i < pl.length; i++){
    node.log(i);
    str = pl[i].d_type;
    node.log(str); //just for debug. To be removed.
    pl[i].d_type = JSON.parse(str);
}

return msg;

```

Any more suggestions will be appreciated.

Thank @E1cid for suggestions.  
F.

---

<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:** [24 March 2022 21:46 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/6 "2022-03-24T21:46:40Z")

</div>

I am a little confused to what you are doing.  
I think you are trying to return an Object to the http response node, and the d\_type field contains a json string

If so try

```auto
[{"id":"6c89bc05.9f3d14","type":"http in","z":"bf9e1e33.030598","name":"","url":"testtest","method":"get","upload":false,"swaggerDoc":"","x":130,"y":1480,"wires":[["ca3e6c07.49be28"]]},{"id":"ca3e6c07.49be28","type":"change","z":"bf9e1e33.030598","name":"simulate db","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"d_type\":\"[\\n\\t{\\\"int_status\\\": \\\"battery\\\",\\\"unit\\\": \\\"volt\\\"}]\"}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":250,"y":1520,"wires":[["7dfb60c8.e37b1"]]},{"id":"7dfb60c8.e37b1","type":"function","z":"bf9e1e33.030598","name":"","func":"var str;\nvar pl = msg.payload;\n// just d_type is text\nfor (var i = 0; i < pl.length; i++){\n node.log(i);\n str = pl[i].d_type.replace(/\\s/g, \"\");\n node.log(str); //just for debug. To be removed.\n pl[i].d_type = JSON.parse(str);\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":470,"y":1500,"wires":[["336dfbae.35a1dc"]]},{"id":"336dfbae.35a1dc","type":"http response","z":"bf9e1e33.030598","name":"","statusCode":"","headers":{},"x":650,"y":1480,"wires":[]},{"id":"c5ebf25e.dff408","type":"inject","z":"bf9e1e33.030598","name":"test end point","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":1400,"wires":[["c2f9d517.a425d8"]]},{"id":"c2f9d517.a425d8","type":"http request","z":"bf9e1e33.030598","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"http://192.168.1.25:1880/testtest","tls":"","persist":false,"proxy":"","authType":"","x":340,"y":1400,"wires":[["e0f8fa3d.eecfd"]]},{"id":"e0f8fa3d.eecfd","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":1420,"wires":[]}]

```

```auto
var str;
var pl = msg.payload;
// just d_type is text
for (var i = 0; i < pl.length; i++){
    node.log(i);
    str = pl[i].d_type.replace(/\s/g, "");
    node.log(str); //just for debug. To be removed.
    pl[i].d_type = JSON.parse(str);
}

return msg;

```

if not, please supply your flow, and the first 2 elements of the returned db array.  
containing field d\_type data. so I can try to work out what you are doing.

---

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [25 March 2022 09:37 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/7 "2022-03-25T09:37:26Z")

</div>

Hello @E1cid, I cannot apply any substitutions, like d\_type.replace(/\s/g, ""), because the response from DB doesn't contain any backslash. **The backslash is applied by http response node.**  
This is the d\_type field from the database:

> {"type":[{"int\_status":"battery","unit":"volt" }, {"sensor":"soil\_temperature","unit":"celsius"}, {"sensor":"soil\_moisture","unit":"relative"}, {"sensor":"soil\_conductibility","unit":"uS/cm"}] }

And this is the row generated on the web page if no function is applied

> "{"type":[{"int\_status":"battery","unit":"volt" }, {"sensor":"soil\_temperature","unit":"celsius"}, {"sensor":"soil\_moisture","unit":"relative"}, \t{"sensor":"soil\_conductibility","unit":"uS/cm"}] }"

So, I'm not able to intercept the backslash.

This is my flow

```auto
[{"id":"b9015c26d1d51798","type":"tab","label":"Flow 3","disabled":false,"info":"","env":[]},{"id":"1d0e1490ef317d6c","type":"mysql","z":"b9015c26d1d51798","mydb":"ebec0657.5b765","name":"db","x":430,"y":120,"wires":[["98cc67b0a2d93966","39a538b0e9779581","198535fe79e3c241"]]},{"id":"86f57b27a47dc656","type":"http in","z":"b9015c26d1d51798","name":"","url":"/devices","method":"get","upload":false,"swaggerDoc":"","x":90,"y":120,"wires":[["f18d73f0cd5e27a8"]]},{"id":"f18d73f0cd5e27a8","type":"template","z":"b9015c26d1d51798","name":"SQL Query","field":"topic","fieldType":"msg","format":"handlebars","syntax":"plain","template":"SELECT d_id, d_netDevID, d_alias, d_pos, d_mobile, d_descr, (d_type) FROM `Devices` WHERE 1;","output":"str","x":270,"y":120,"wires":[["1d0e1490ef317d6c"]]},{"id":"198535fe79e3c241","type":"http response","z":"b9015c26d1d51798","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":870,"y":120,"wires":[]},{"id":"98cc67b0a2d93966","type":"debug","z":"b9015c26d1d51798","name":"DB-response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":200,"wires":[]},{"id":"a749a9fb73913f22","type":"debug","z":"b9015c26d1d51798","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":910,"y":260,"wires":[]},{"id":"39a538b0e9779581","type":"function","z":"b9015c26d1d51798","d":true,"name":"lontext to json","func":"var str;\nvar pl = msg.payload;\n// just d_type is text\nfor (var i = 0; i < pl.length; i++){\n node.log(i);\n str = pl[i].d_type;\n node.log(str);\n pl[i].d_type = JSON.parse(str);\n}\n\n//msg.payload = pl.lenght;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":640,"y":80,"wires":[["a749a9fb73913f22","198535fe79e3c241"]],"info":"**Convert MySql JSON (In fact a string) to json object**"},{"id":"ebec0657.5b765","type":"MySQLdatabase","name":"","host":"10.0.0.114","port":"3306","db":"sensors","tz":"","charset":""}]

```

This image is the screenshot of the web page.

 ![immagine](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/9/593ab902d6715984574a31b6722e4bf3eee9ee9f.png)

---

<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:** [25 March 2022 16:30 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/8 "2022-03-25T16:30:36Z")

</div>

Please supply the array from db , only first 2 elements of array. I need to see the whole format. Use the copy value from debug sidebar. Then edit to only 2 elements of array and paste text here

---

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [25 March 2022 16:48 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/9 "2022-03-25T16:48:44Z")

</div>

Copy and past add the backslash

```auto
[{"d_id":1,"d_netDevID":"eui-a84041f6b1821ee7","d_alias":"Soil_data","d_pos":{"x":44.69327,"y":10.098514},"d_mobile":0,"d_descr":"Soil Data","d_type":"{\"type\":[{\"int_status\":\"battery\",\"unit\":\"volt\" }, {\"sensor\":\"soil_temperature\",\"unit\":\"celsius\"}, {\"sensor\":\"soil_moisture\",\"unit\":\"relative\"}, \t{\"sensor\":\"soil_conductibility\",\"unit\":\"uS/cm\"}] }"},{"d_id":3,"d_netDevID":"stm-wl","d_alias":"Tank level","d_pos":{"x":44.69327,"y":10.098514},"d_mobile":0,"d_descr":"","d_type":"{\"type\":[{\"sensor\":\"tank_level\",\"unit\":\"mm\"}] }"}]

```

but the picture from node-red's debug doesn't

 ![immagine](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/9/79fce499a889c3fc762a10f0a2e04971b4b0eb65.png)

🙄

---

<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:** [25 March 2022 17:09 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/10 "2022-03-25T17:09:58Z")

</div>

Ok there is a stray tab in your db return( see image where my cusor is)

 ![Screenshot_20220325-170334](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/0/d0c2be8c60e5ba15d4110022b48988a3992d3c3f.png)  
When i run your flow with data supplied i get no issues with it.  
test flow

```auto
[{"id":"86f57b27a47dc656","type":"http in","z":"b9015c26d1d51798","name":"","url":"/devices","method":"get","upload":false,"swaggerDoc":"","x":100,"y":120,"wires":[["f18d73f0cd5e27a8"]]},{"id":"f18d73f0cd5e27a8","type":"template","z":"b9015c26d1d51798","name":"SQL Query","field":"topic","fieldType":"msg","format":"handlebars","syntax":"plain","template":"SELECT d_id, d_netDevID, d_alias, d_pos, d_mobile, d_descr, (d_type) FROM `Devices` WHERE 1;","output":"str","x":270,"y":120,"wires":[["2b71def.c1a58a2"]]},{"id":"2b71def.c1a58a2","type":"change","z":"b9015c26d1d51798","name":"simulate db","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"d_id\":1,\"d_netDevID\":\"eui-a84041f6b1821ee7\",\"d_alias\":\"Soil_data\",\"d_pos\":{\"x\":44.69327,\"y\":10.098514},\"d_mobile\":0,\"d_descr\":\"Soil Data\",\"d_type\":\"{\\\"type\\\":[{\\\"int_status\\\":\\\"battery\\\",\\\"unit\\\":\\\"volt\\\" }, {\\\"sensor\\\":\\\"soil_temperature\\\",\\\"unit\\\":\\\"celsius\\\"}, {\\\"sensor\\\":\\\"soil_moisture\\\",\\\"unit\\\":\\\"relative\\\"}, \\t{\\\"sensor\\\":\\\"soil_conductibility\\\",\\\"unit\\\":\\\"uS/cm\\\"}] }\"},{\"d_id\":3,\"d_netDevID\":\"stm-wl\",\"d_alias\":\"Tank level\",\"d_pos\":{\"x\":44.69327,\"y\":10.098514},\"d_mobile\":0,\"d_descr\":\"\",\"d_type\":\"{\\\"type\\\":[{\\\"sensor\\\":\\\"tank_level\\\",\\\"unit\\\":\\\"mm\\\"}] }\"}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":160,"wires":[["98cc67b0a2d93966","39a538b0e9779581"]]},{"id":"98cc67b0a2d93966","type":"debug","z":"b9015c26d1d51798","name":"DB-response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":200,"wires":[]},{"id":"39a538b0e9779581","type":"function","z":"b9015c26d1d51798","name":"lontext to json","func":"var str;\nvar pl = msg.payload;\n// just d_type is text\nfor (var i = 0; i < pl.length; i++){\n node.log(i);\n str = pl[i].d_type;\n node.log(str);\n pl[i].d_type = JSON.parse(str);\n}\n\nmsg.payload = pl;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":630,"y":120,"wires":[["198535fe79e3c241","a749a9fb73913f22","98cc67b0a2d93966"]],"info":"**Convert MySql JSON (In fact a string) to json object**"},{"id":"198535fe79e3c241","type":"http response","z":"b9015c26d1d51798","name":"","statusCode":"","headers":{"content-type":"application/json"},"x":870,"y":120,"wires":[]},{"id":"a749a9fb73913f22","type":"debug","z":"b9015c26d1d51798","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":890,"y":200,"wires":[]}]

```

after function node

 ![Screenshot_20220325-170634](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/7/97e477d6917139392042e3e1a38dd3d20a33d7fe.png)  
response in browser  
 ![Screenshot_20220325-170820](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ec7655d447244d854d7d5a6c704246c799bd653d.png)  
Where are you seeing the `\t` representation of the tab, and what exactly is the issue

---

<div class="post-metadata">

**Author:** ![rex](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rex/32/59848_2.png) [@rex](https://discourse.nodered.org/u/rex)\
**Post date:** [26 March 2022 14:43 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/11 "2022-03-26T14:43:52Z")

</div>

This is a small problem, but not the problem.  
I've rewrited d\_type field using:

```auto
select * from Devices;
update Devices set d_type=JSON_COMPACT(d_type);

```

and now no extra chars are on the field, but the behavior doesn't change.  
I'll look better at how to do the SQL/JSON manipulation, but there are some small differences between MySQL console output and node-red query results. (E' un mondo difficile).

For now, I use my approach, it seems working, then I will look for a better method.

---

<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:** [25 May 2022 14:44 UTC](https://discourse.nodered.org/t/mysql-query-with-json-fields/60256/12 "2022-05-25T14:44:02Z")

</div>

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