Node-red Json to database writing

General
1

Hello everybody,
i am a newbie in node-red. I want to use it to write a json file into a PostgreSQL database, where the json file should be divided into different tables and written in a database. Is there an easy way to do that?

example json file:
{ 'location': 100 ,
'job_id': 1,
'operator': 'Jenny',
'curve': {'time_series':[1,2,3,4,5,6], 'Momentum':[1,2,3,4,5,6]},
}
I want a table of:
data with columns location, job_id, operator curve and another table named curve with columns time_series and Momentum, is that possible with Node-Red.

2

Most definitely.

Something to get you started...

image
image1229×189 21.5 KB

demo flow (use CTRL+I to import)

[{"id":"9358d3d721f4b18f","type":"inject","z":"85e1791bc0cd6285","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"location\":100,\"job_id\":1,\"operator\":\"Jenny\",\"curve\":{\"time_series\":[1,2,3,4,5,6],\"Momentum\":[1,2,3,4,5,6]}}","payloadType":"json","x":2330,"y":1282,"wires":[["e974f54d76d72765"]]},{"id":"e974f54d76d72765","type":"change","z":"85e1791bc0cd6285","name":"","rules":[{"t":"move","p":"payload.curve","pt":"msg","to":"curve","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":2362,"y":1334,"wires":[["4feffe5c5fd46429","e4936e949e7d4801"]]},{"id":"4feffe5c5fd46429","type":"function","z":"85e1791bc0cd6285","name":"Built insert Query","func":"msg.payload =  `INSERT INTO t1 (location, job_id, operator)\nVALUES (${msg.payload.location}, ${msg.payload.job_id}, '${msg.payload.operator}')`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2632,"y":1334,"wires":[["ab0bcbd8a6aff070"]]},{"id":"e4936e949e7d4801","type":"function","z":"85e1791bc0cd6285","name":"Built insert Query2","func":"\nconst ts = JSON.stringify(msg.curve.time_series)\nconst mom = JSON.stringify(msg.curve.Momentum)\nmsg.payload =  `INSERT INTO t2 (time_series, Momentum)\nVALUES ($'{ts}', '${mom}')`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":2632,"y":1394,"wires":[["4666a71a77e9d22a"]]},{"id":"ab0bcbd8a6aff070","type":"debug","z":"85e1791bc0cd6285","name":"To Database →","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2862,"y":1334,"wires":[]},{"id":"4666a71a77e9d22a","type":"debug","z":"85e1791bc0cd6285","name":"To Database →","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2862,"y":1394,"wires":[]}]

PS

in order to make code readable and importable it is important to surround your code with three backticks
```
like this
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

PS2

As a new user, I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

3

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