I'm trying to send 4 values to the 4 columns of the database but I'm not able to put the payloads correctly because the three values are inserted in each column and I wanted to separate each one of them into the corresponding column, so the timestamp is that it's correct. My function is this:
and the database table output is this:
how can I get the correct values in each column?
What does msg.payload contain? Can you provide an example? I guess it's a string containing all of your values in some format and you need to split up to get the individual values.
I have 3 msg.payload for 3 sensors of temperature the msg.paload is a number, the debug values is this:
yes I need to split them to get the values of each sensor
So to be clear, you don't have one message containing all values. You have multiple messages, each one containing a different sensor's reading.
So this is not a question of splitting the payload, but you need to join those separate messages into one.
Have a look at the Join node - you can use that to join multiple messages into one - which you can then pass to the Function node to build the appropriate SQL Insert statement.
Also, your insert statement uses msg.payload three times in the VALUES section so you will get three identical values in the three columns
It will be better to restructure your database so your "sensors" table is structured like "date,sensor,value". When writing the SQL to display you can use sub queries and "group by" to generate the output you require if still want in columns.
PS. If it's purely for timebased data, InfluxDB is a better DB for this.
I configured the join like this:
No. The split node will take that one message and give you back three separate messages. That isn't what you want. You need one message to arrive at the Function node containing all the data you want to insert as a single row - which is what the Join node has given you.
Pass that message to your Function node. You can then access they three separate values as msg.payload[0], msg.payload[1] and msg.payload[2] (because msg.payload is now an array).
Configure the Join node to generate Key/Value pairs then you will be able to identify which one is which. Make sure the messages coming in have different topics then you can reference them by name. You might need to think about what happens if one of them is missing. Perhaps you need to specify a timeout there too, so it will send just what it has received after the timeout if one or more are missing.
Like this or i need define variable to msg.payload[0]
My schematic is this:
but not send the data to database
I see you have a Debug node wired to the Function node output. It will show the query you are sending to the database - check it looks right.
As @Colin suggested, you may also want to change the Join node configuration to create a key/value object. At the moment, the readings will be in whatever order they happen to arrive - which may not be consistent.
Also most databases have the ability to configure a timestamp field that is automatically set to the current time when you add a record. That is much better than inserting it yourself.
the debug don´t show anything, i'm not shure if the query of msg.topic in function is the correct way for send the data to the database or i need to create variables to msg.payload[0], msg.payload[1] and msg.payload[3], can help me in the function?
Change the Debug node to display msg.topic - as that is the property you are setting and is the property you want to check the value of.
Not appears anything in debug !!!
Is anything appearing in the "Input" debug? If not then it may be because the join node is waiting for three values.
That Join will never send anything as you have not told it how many message parts to expect. You probably want it set to 3.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy
