Node-red + postgres

Hello everyone!
I have a problem with converting JSON string to a database postgreSQL.

How I can put JSON strin into a table ?

I tried that:


var myDataArray = JSON.parse(msg.payload.split(','));
var heading = myDataArray[0];
var latitude = myDataArray[1];
var longitude = myDataArray[2];
var speed = myDataArray[3];
var trip_id = myDataArray[4];

msg.topic = "INSERT INTO `car-data`.`car-data-table` (`heading`,`latitude`, `longitude`, `speed`, `trip_id`)      VALUES ("+heading+","+ latitude +", "+longitude+", "+speed+", "+trip_id+")";
return msg;

But I have an Error and do not know what I need to do.

Could you help me?

My JSON input string seems like that

{"heading":274.316779,"latitude":30.398874,"longitude":-97.747797,"speed":19.958889600000003,"trip_id":"1457671273"}

What does the data look like before you feed it through the JSON node, and why are you doing that?

I just need to put JSON data into database.
It is JSON from the begining.

Can you define what you mean by that please, it looks to me as if you are trying to write property values to the database not JSON.
Also if it is JSON to start with (which means it is a string representing a javascript object) then why have you got the JSON node there?

Can you put a debug node showing what is going into the JSON node?

My last version of function:

var myDataArray = msg.payload;

var heading = myDataArray.d.heading;
var latitude = myDataArray.d.latitude;
var longitude = myDataArray.d.longitude;
var speed = myDataArray.d.speed;
var trip_id = myDataArray.d.trip_id;

msg.topic  = "INSERT INTO `trip` (`trip_id`)      VALUES ("+trip_id+")";
msg.topic = "INSERT INTO `location` (`heading`,`latitude`, `longitude`, `speed`)      VALUES ("+heading+","+ latitude +", "+longitude+", "+speed+")";
return msg;


My debug nodes:

@saf

As @Colin mentioned, your SQL queries do not "write JSON" to your tables. You are simply inserting values.

Normally what I do is to make sure the data types in the DB table schemas match the SQL query.

Please post your table schema / descriptions.

Cheers

You do realize that the `insert into 'trip'....' is being over written by the second statement...right?

1 Like

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