Writing a groov table with a single node


#1

The simplest way to get an array from Node-RED to a groov data store table is to make a single write node for each of the tag’s indices, which is all good and well for small tables but for larger lists this quickly becomes ugly and cumbersome.
One work around I have found is to use JavaScript in a function node to turn the list into a message payload array of specially constructed objects that gets separated by a split node to write many values, even an entire table, using only a single groov node.

First you’ll need a data store with a table in it. For this example I have a data store NodeRED with a new tag called String_List that can hold a table of 5 strings.

I saved the tag and put 5 value gadgets on a groov page to show the content of each index, in groov view I can see the table is empty:

Now in Node-RED I use four nodes: inject, function, split, and groov write, in that order.

The inject and split nodes can keep their default settings, and the function node uses this code to turn my list sentence into an array of objects:

var sentence = ["Luke","I","Am","Your","Father"]; // Some table/array of data.
var data = []; // A new array to hold the tag, indices, and values for groov.
for (i = 0; i < sentence.length; i++) // Go through the list
{
    data.push({
        tagName         : 'String_List',
        tableStartIndex : i,
        value           : sentence[i]
    }); // Each 'data' object will be one groov write
}
return { payload : data }; // This new array is the payload, soon to be split!

The output of which looks like this:

which splits into:

Note that they each take the following form, which will write whatever data (String, Float, Integer, or Boolean) to the tag MyTagName at index i:

msg.payload = {
  tagName         : 'MyTagName',
  tableStartIndex : i,
  value           : data
} // one "write object"

To have the groov node accept this object, set it up the data store (in my case NodeRED), blank Tag Name and Index, and msg.payload.value as the Value.

The groov write node takes the rapid-fire stream of payload objects from the split node and writes each msg.payload.value to the groov tag String_List -as specified in the function node- at each index i = 0, 1, 2, 3, with each item from my sentence array.

Now I can deploy and inject the flow to see the sentence appear in groov View:

This is a very simple example, it is possible to use a payload array of that basic “write object” format to get all sorts of things into a groov data store with only a single write node!

Happy coding!


Putting groov log into a groov data store - Read intro on first post!
Write to a groov Data Store table with Node-RED
Rolling FIFO data stream to a groov data store table
#2

To expand on this method, I’ve been working on a Node-RED flow that pulls in data from a Microsoft SQL database and displays it on a groov page.
To make building the flow easier, the flow itself smaller, and certainly make maintenance easier I have written a bit of JavaScript that takes multiple rows of data and pushes it into six different groov tags at many indices all using a single write node thanks to the dynamic writing object in the post above.

When I SELECT three rows of data from the mssql node it comes back like this:

Which is a great format to sort through since each payload index represents one row, with each property being a column I select from the database, in this case six columns from the Opto turbine demo.
But this could be information from any other database or any other data source, even in a different format!
For example I often have data in Node-RED look like this:

The important thing is that I have it in arrays in Node-RED, and I want the information in groov data store tables, each of length 3.

Regardless of the format in Node-RED, I want to be going through the arrays and push each property into a big array of write objects that follow the same dynamic template:

msg.payload = {
  tagName         : 'MyTagName',
  tableStartIndex : i,
  value           : data
} // one "write object"

Unlike the post above, however, I am not using the same tag for every single write, instead I am going to switch between the six tags time, power, spot_price, RPM, wind_speed, and temperature - one for each column of data - and I’m going to push three times that, once for each row.
This means I can write to eighteen unique gadgets in groov using a single write node, and it would be as simple as increasing the table size to any number ## and changing my query to SELECT TOP ## and Node-RED will dynamically take on the extra data – so I am not by any means limited to only eighteen.

The JavaScript will look a little different depending on the original form of your data, but going through the MS SQL payload array, my code takes on this framework:

var data = [];
actualsize = msg.payload.length; // How many rows were actually returned?
for(i = 0; i < actualsize; i++)
{
    data.push({ tagName : 'first_tag',
                tableStartIndex : i,
                value : msg.payload[i].<first_column> });
    data.push({ tagName : 'second_tag',
                tableStartIndex : i,
                value : msg.payload[i].<second_column> });
    data.push({ tagName : 'third_tag',
                tableStartIndex : i,
                value : msg.payload[i].<third_column> });
    // . . . epeat for each column -> tag
}
return { payload : data }; // this object array becomes the new payload

Which in my case comes out looking like this:

Now it is going to seem like I really bloated out my payload, since I went from an array of length 3 to an array of length 18:

However this feeds straight into a split node to turn it into 18 separate payloads, and then one groov write node just like the above example, so all together my flow is quite small:

Where the final write node has Tag Name and Index fields empty so that it takes them dynamically from the payload propertes.

Using this one node I am writing to 18 gadgets composed of 6 different tags with 3 indices each:

Here is a roughly equevalent flow that uses a single gadget per write…

The other cool thing is that if I increase each table to take 4, 5, or even 50 table values it’s as simple as changing my select statement to select the TOP 50 instead of the TOP 3, and the write node dynamically keeps up, I don’t have to add more nodes!
(Of course 50 x 6 rows would require 300 gadgets to display, but I can keep the data in datastores without necessarily displaying them all, it all depends on what data you want, and what you want to do with it.)

Happy coding!


#3

This is great reading. Will try to implement this in testing soon. :smiley:


#4

Tom,

I followed you up until the split…can you explain the split.

Also, I am trying to update SNAP PAC S1 tables using data from SQL queries. I can get the data from the SQL database into a msg.payload array in node-red, but I cannot find any examples on how to move the array values to the PAC S1 tables using the PAC write node.

Thanks,
Craig