Mysql query with json fields

[rex]

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

[E1cid]

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.

[rex]

@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

[E1cid]

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

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

[rex]

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:

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.

[E1cid]

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

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

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.

[rex]

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

[{"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.

[E1cid]

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

[rex]

Copy and past add the backslash

[{"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

[E1cid]

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

When i run your flow with data supplied i get no issues with it.
test flow

[{"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

response in browser

Where are you seeing the \t representation of the tab, and what exactly is the issue

[rex]

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

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.