Writing a groov table with a single node

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!

2 Likes

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!

1 Like

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

Terry,

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

Hi Craig,
This trick works great for groov write nodes and multiple variable names, but if you’re using PAC tables then you can write entire tables directly. With the array in Node-RED like you said, msg.payload = [ ... ]; can go straight into the PAC write node, the node will handle the table indices for you.

A simple formation like this should do what you’re looking for:

With these settings for the Snap PAC write node (strings is my PAC control string table):
image

This will copy across every entry you provide, and can be offset with the “Start Index” if you want to add items to the end of a table and not overwrite the whole thing.

From Node-RED:
image

To PAC Control:
image

How would you insert a dynamic array from another node into here?
Screenshot 2021-05-27 183406

I have function block in use… Its called ‘Array to JSON’.
I think it will do what you are looking for (If I understand your request correctly).

Here is the JavaScript in it:

var IP,Country,State,Town;

IP = msg.payload[2];
Country = msg.payload[4];
State = msg.payload[5];
Town = msg.payload[6];

msg.payload = ([{IP:IP,Country:Country,Town:Town}])
return msg

Note I did not end up using ‘State’, but left it in there to remind me what index of the array it was (5).

Edit… wait, you said dynamic…
In that case, you need to get the length of the array.
Here is what Terry wrote for me in my application (counting aircraft), I think with a little tweaking, it should work for you - I have already changed my code to suit your msg.payload.

var m = [];
for (var i in msg.payload.Classes)
{
        msg.sqk = (msg.payload.Classes[i].Url);
        msg.abxurl = ("https://tar1090.adsbexchange.com/?icao="+msg.payload.Classes[i].Url);
        msg.faurl = ("https://flightaware.com/live/flight/"+msg.payload.Classes[i].Url);
        return msg; // return the full msg including the new properties.
    
}
return null; // no matches found. end of line.

You probably will end up putting that first bit of code in the middle of this loop, so you make the JSON object (key : value) no matter what size your array.

Final edit:
This code is even simpler and may be all you need:

var m = [];
for (var i in msg.payload.Classes) {
    m.push({payload:msg.payload.Classes[i].Url});
}
return [m];

OK so, using this:

var m = [];
for (var i in msg.payload.obj.Classes) {
    m.push({payload:msg.payload.obj.Classes[i].Id});
}
return [m];

I get this:

5/29/2021, 11:05:34 AM[node: 168834e3.c099a3](https://192.168.1.90/node-red/#)msg : Object

{ payload: 4311744514, _msgid: "6ba6fc52.bada04" }

5/29/2021, 11:05:34 AM[node: 168834e3.c099a3](https://192.168.1.90/node-red/#)msg : Object

{ payload: 4311744515, _msgid: "6ba6fc52.bada04" }

etc...

How do I use the SPLIT node on this? Nothing I have tried has output any data.

Best as I can see, there is no data in the payload, so hence you are not getting any out.

The payload is the 11 digit number. It shows up as a payload as observed here:

This is directly out of the function block.

Ok, I see it now.

Why are you trying to split? I thought you wanted it in an array?

I want to write these to groov in one shot while also taking care of the dynamic aspect of it. Sometimes there are 5, sometimes there are ten, the size can vary from 1 record to 40 and I need to be able to write zeros (or something) for the null/empty records on the groov. Otherwise I just get errors in node-red about “undefined” records and the corresponding records on the groov remain filled with the previous poll.

Edit: I’m trying to follow @torchard’s instructions/example. He used the function node to build a new JSON array and he used the split node to push all of the records into the groov write node one record at a time. (If I follow correctly)

Ah, Ok, starting to see a little better what you are trying to do.
This covers it nicely:

No, I don’t want a FIFO stream. I want a complete array rewrite every time that can handle a variable (dynamic) length input array and overwrite any empty (undefined) records with a zero (or something) on the groov.

My input array can vary from 1 to 40 records. I use that “classID” to look up statistics for that class in near real-time. I additionally will poll another dynamic array with each ID to display pertinent information regarding that ID at specific locations simultaneously.

I can do it one node at a time, but it’s tedious and harder to catch errors. Also, I haven’t figured out how to deal with undefined records.
This is just the beginning:

I would much rather it be something like this:

Instead of looping through the values you do have, I would recommend looping through all 40 and only write the defined values, otherwise write a placeholder like zero. That way the first 13 (or however many values) will get written and the last 27 still get written to, they just get overwritten with zero instead of some other value.

To make this dynamic and avoid the split node, you can just use node.send({payload : ...}) and an if statement to check to see that the value is good.
Also, note that this node.send() returns individual payloads, so there’s no need for a return statement at the end of the code.

for(i = 0; i < 40; i++) {
// If this value exists, write it to the list:
if(typeof(msg.payload.Classes[i]) !== "undefined") {
    node.send({ payload: { tagName : 'id',
                tableStartIndex : i,
                value : msg.payload.Classes[i] }});
}
// Write zero for undefined values and values not provided by the payload:
else node.send({ payload: { tagName : 'id',
                tableStartIndex : i,
                value : 0 }});
}

So this would exist right after your “Join” function node and would not rely on a split node.
@nickvnlr do you think this is the kind of approach that you’re looking for?

1 Like

I got this to work yesterday on a smaller array with the split node, but it still doesn’t overwrite undefined values.

var sentence = msg.payload.obj.ExitIds; // 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         : 'TestArray',
        tableStartIndex : i,
        value           : (sentence[i])+1
    }); // Each 'data' object will be one groov write
}
return { payload : data }; // This new array is the payload, soon to be split!

I’ll play with the new coding and see where I get. At some point when I get more time I will put together a “Dummies” version of all of this. I am not a programmer by trade and had to fumble my way through this.

Thanks.

The key is to do the ‘typeof’ check. That’s what looks for your ‘undefined’ and writes a zero in that location.

I think once you put Terrys code in place you should be a lot closer to getting where you want to be.

1 Like

Would you put this into the same function node or create a new function node?
Thanks.

Whatever works best for you, honestly either way is fine. You may find it easier to modify and debug if it’s a separate node, but it might be easier to work on if you can see all the code at once.

It’s more personal preference than a real functional difference since all the code is going to run either way.

1 Like

When I run this:

var sentence = msg.payload.obj.Classes; // 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         : 'Class ID',
        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!

and then this:

for(i = 0; i < 40; i++) {
// If this value exists, write it to the list:
if(typeof(msg.payload[i]) !== "undefined") {
    node.send({ payload: { tagName : 'Class ID',
                tableStartIndex : i,
                value : msg.payload[i] }});
}
// Write zero for undefined values and values not provided by the payload:
else node.send({ payload: { tagName : 'Class ID',
                tableStartIndex : i,
                value : 0 }});
}

and inject msg.payload.value.value.Id into groov write I get results plus this error:

6/2/2021, 12:00:56 PM[node: Class 0](https://192.168.1.90/node-red/#)

msg : string[38]

"Cannot read property 'Id' of undefined"

Where did I screw up?