Hi,
I have Node-Red and PostgreSQL running on a RasPi4.
From Node-Red I can Create and Drop tables and I can write data into a db table using a standard INSERT statement, so I'm happy the basics are working.
I am picking up some data from an API which is in JSON format.
I just can't figure out the syntax to write the data to write it into the database.
Seemingly I have tried combinations of 1, 2 and even 3 sets of {}
I have tried with ( and ) and also [ and ] also with single 'data' and "data"
Much Googling and YouTubing.
Also the timestamp with timezone seems particularly difficult to work with.
I can't see conclusively which Node's to select to make the whole thing flow so I'm not sure posting my actual anything is valuable. I have tried so many combinations.
Without more details there is nothing we can do to help. Show us the incoming data (feed it into a debug node) and tell us the query you are trying to generate from that data.
That's fair.
Here is the message received straight out of the Debug node.
2/24/2020, 8:28:14 PM[node: cde20083.11da3]
msg.payload : Object
object
name: "TWTR"
history: object
2020-02-18: object
open: "36.78"
close: "38.06"
high: "38.07"
low: "36.68"
volume: "16821347"
My aim is to select the Table called SNAP and write the fields as received into the columns.
I have been trying the MYDB node with zero success so far.
If I use the MYDB node on it's own with an insert statement and manually put the data in the insert statement it works. This tells me I can access and write to the table.
I have tried real basics.....
Take the Inject Timestamp node and try to write the msg.payload into a table with the first column as timestamp and just can't get it right.
What node would you suggest?
Does anyone know of a Tutorial, HowTo or just a link to someone who has written msg.payload to a table in PostgreSQL?
Which node is that? Possibly node-red-contrib-something.
Most database types allow you to specify a column which will automatically add the current time into a row when you insert it.
You didn't answer the question, what should the insert query for the above data look like?
Not sure whether that will show up but it is one of the PostgreSQL nodes and has the little elephant (it is blue)
As opposed to the node-red-contrib-postgres
I was persevering with the MYDB node only because I could prove the ability to write data into the table.
I specifically want to keep the Date/Time info that returns from the API and not the current Date/Time
The table has the other columns set up in order so....
Table name is TWTR
date
open
close
high
low
volume
ready to receive the returned info as per the Debug payload data
There is a node called postgrestor which conflicts with node-red-contrib-postgres and I just don't know which one is better to install and try as you can only have one or the other.
You have not shown a timestamp in the debug output you posted. Can you fully expand the payload and post a screenshot of the debug output so we can see exactly what it there.
Here is the message received straight out of the Debug node.
2/24/2020, 8:28:14 PM[node: cde20083.11da3]
msg.payload : Object
object
name: "TWTR"
history: object
2020-02-18: object
open: "36.78"
close: "38.06"
high: "38.07"
low: "36.68"
volume: "16821347"
This particular API return has date only and not timestamp - my apologies
I have a table called TWTR
The columns are as follows:
date
open
close
high
low
volume
My aim is to write the data received from the api and put the relevant data in the relevant column within the table.
So
date would be 2020-02-19
open would be 36.78
etc
The insert statement looks like this...
INSERT INTO public."SNAP"(
date, open, close, high, low, volume)
VALUES (?, ?, ?, ?, ?, ?);
What I don't know is how to set the syntax for all the ? to unpack the JSON data
Thanks for looking at my query. I wish I knew where to look but just can't find anything close.
Cheers
Richard
The reason I asked for a screenshot was to make this bit more clear. Is that an attribute called "2020-02-18" containing the open, close values etc? If so that is not a nice way to pack the data as you will have to interrogate the keys of the object. Is that contained within the history attribute? Can there be more that one date object in the history object?
Also what type is the date column in the db?
In the db I have created the date column as the primary key and it is of data type date.
I can easily change this if I have made a mistake.
Your other questions are tough for me.
When I query the API, I can nominate a date as I did above or I could put in a range of dates in which case the returned data repeats with the date object followed by the open, close etc for that date. But I need to start simply and get one going and this appears to be the smallest/simplest (says me choking on it)
I'm kind of hoping once I see how to write one of these returned API datasets into PostgreSQL that I can figure the rest out. There are many queries I can construct and this is possibly the simplest.
But, I can't change what I get back or how it gets presented.
I have found a node called data mapper and am trying to find an example on YouTube of it's use.
Thanks for your continued support.
Richard
That means that you will only be allowed to have one row with that date. Better is to defind a field like id and have is an auto-incrimented number as the primary key.
In your debug you can see that '2020-02-18' is the 'key' of a key/value pair so to grab it you will need something like this in a function node var k = Object.keys(msg.payload.history)[0]; // returns first
@zenofmud thank you for the starter above. I look forward to having a go at this tonight.
For this table there will only be one entry per day so PKey of the date field is fine. There will be another table where the timestamp will come from the API and it can be the Pkey. It was certainly a consideration though.
It's exciting to be putting data into the database (hopefully) and I appreciate the assistance.
Cheers
Richard
