Read fields from arrays in object

General
1

Hi,

I am new into node-red and JavaScript. I am trying to read an object consisting of multiple arrays and insert some fields per array into a sql database (Sqllite). I have tried the split node and ended with trying to create some JavaScript consisting of a for loop to read the arrays and extract the fields.....

If anybody could give me the hint, I would be very grateful, thx in advance

See attachement for the structure of my object

Object_structure
Object_structure.JPG708×837 50.7 KB

2

and the javascript you tried?

3

What are you trying to get out of the data?

4

var i;
var sql="";
var outputs=[];
var id;
var name;

for(i=0; i<payload.NbrOfRows; i++) {
id=msg.payload.Rows[i].Id;
name=msg.payload.Rows[i].Name;
sql="INSER INTO devices(id,name) " +
"VALUES ("+id+","+name+")";

outputs.push=({topic:sql});

}

This is what I have tried...composed from different inputs :grinning:

5

For a start
payload.NbrOfRows does what? to what?

If you mean the length of the Array look at https://www.w3schools.com/jsref/jsref_length_array.asp

Rather than try to do it all at once break it down into easy chunks. do each chunk one at a time.

So start by getting the javascript correct for the length of the array. You might also want to look up the SQL query to add an entry into a database table

6

The = should not be there.

That should be INSERT

Then if you get an error from the function that you don't understand show us what it is. Otherwise put a debug on the output and see what it says.
What are you doing at the end of the function? If you want to send multiple messages to one output then it needs to be an array containing an array so you would need something like
return [outputs]

7

Hi,

It's working now, except for my typos and missing "msg.", I needed to place some "" around the id and name. This is the final working code:

var i;
var sql="";
var outputs=[];
var id;
var name;

for (i=0; i< msg.payload.NbrOfRows; i++) {
    id=msg.payload.Rows[i].Id;
    name=msg.payload.Rows[i].Name;
    sql="INSERT OR REPLACE INTO devices(id,name) " + 
    "VALUES (\""+id+"\",\""+name+"\");";
    outputs.push({topic:sql});
}

return [outputs];