asked on

Unable to extract Jason data into SQL

Hi, i am runing program on Node-Red. The data format in JASON. I am trying to pull out "payload": "Open" and insert into mysql. No errors but the data in sql state as [object OBJECT] . Did attached the database field.

test2 : msg : Object
object
topic: "test2"
payload: "Open"
qos: 0
retain: false
_topic: "test2"
_msgid: "2356ede6.954ba2"

Open in new window

var temp;

temp ={"payload":msg.payload};
msg.topic = "insert into ctesting (data1) values ('"+temp+"')";


return msg;

Open in new window

db.png

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rama Tito

ASKER

"TypeError: json.Replace is not a function"

I did tryout with this code: -

var obj = msg.payload;

var json = JSON.stringify(obj);

msg.topic = "INSERT INTO ctesting (data1) VALUES ('" + json + "')";
return msg;

Open in new window

its works but data in database updated with " " (double quote). Need to remove that.

Rama Tito

ASKER

I do manage to get it with following code after google on w3school

var obj = msg.payload;
var json = JSON.parse(obj);

var Device = json.Device_ID;
var Line = json.Line_ID;
var sum = json.Count;
var dt = json.Date_Now;

msg.topic = "INSERT INTO ctesting (data1,data2,data3,data4) VALUES ('" + Device + "','" + Line + "','" + sum + "', '"+ dt+"')";
return msg;

Open in new window

Rama Tito

ASKER

Hi Experts,

Its help me to kick start from where i am. Thank you

ste5an

"replace" starts with an lower-case r, sorry for that typo.

But it's important, that you use it, to avoid SQL injection.