Can I skip undefined in array in a function node?

[Nice_to_have]

Good day everyone,

I come up with a hobby project which for sure is keeping me active on this forum.
Searching for, and learning a lot from, other forum entries.
I have searched the forum for topics that might tell me how to handle undefined in an array.
But i haven't come accross one that pointed me in the right direction, but that could be the result of the way I search as well.

This sort of comes from this topic: How to store & update NMEA-AIS data?

I am getting an array from which some parts are 'undefined'.
I have a function node that prepares it for MYSQL. But the node of MYSQL doesn't write anything to the database.

This is my function node:

msg.topic = "INSERT or REPLACE INTO AIS VALUES ( " + msg.payload[0].mmsi + "," + msg.payload[0].channel + ", " + msg.payload[0].messageType + ","+ msg.payload[0].repeatIndicator +"," + msg.payload[0].navigationStatus+","+msg.payload[0].sog+","+ msg.payload[0].positionAccurate+","+msg.payload[0].lon+", "+msg.payload[0].lat+","+msg.payload[0].timeStamp+","+msg.payload[0].raim+","+msg.payload[0].radioStatus+","+msg.payload[0].talkerId+","+msg.payload[0].sentenceId+","+msg.payload[0].talkerId_text+","+msg.payload[0].sentenceId_text+","+msg.payload[0].messageType_text+","+msg.payload[0].navigationStatus_text+","+msg.payload[0].bearing+","+msg.payload[0].distanceKM+","+ msg.payload[0].distanceNM+","+msg.payload[0].version+","+msg.payload[0].shipId+","+msg.payload[0].callsign+","+msg.payload[0].name+","+msg.payload[0].shipType+","+msg.payload[0].dimensionToBow+","+msg.payload[0].dimensionToStern+","+msg.payload[0].dimensionToPort+","+msg.payload[0].dimensionToStarboard+","+msg.payload[0].fixType+","+msg.payload[0].eta+","+msg.payload[0].draught+","+msg.payload[0].destination+","+msg.payload[0].dte+","+msg.payload[0].shipType_text+","+msg.payload[0].fixType_text+","+msg.payload[0].turningDirection+","+msg.payload[0].cog+","+msg.payload[0].trueHeading+","+msg.payload[0].turningDirection_text+","+msg.payload[0].originalMessage+")"
return msg;

But what leaves the function node is a lot of 'undefined'.

INSERT or REPLACE INTO AIS VALUES ( 013646568,A, 1,undefined,undefined,0,true,5.520333333333333, 53.18111833333333,undefined,true,0100000100001110001,AI,VDM,Mobile AIS station,AIS VHF data-link message,Position Report Class A,undefined,251.73879507739878,2.9603108494349866,1.5984399732661974,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,undefined,0,undefined,undefined,!AIVDM,1,1,,A,10=0rr?P00PIAB@NKS?h0?wP2PQi,0*6C)

MYSQL doesn't show any errors:

I assume this is because of the undefined in the array.
How can I remove the undefined?

The undefined parts might come later in another message, but that message will have some undefined as well.
Do I need to make different arrays (one for each type of AIS message, or can I keep it in one and filter out the undefined?

this array goes into the function node:

[{"channel":"A","messageType":1,"mmsi":"013646568","sog":0,"positionAccurate":true,"lon":5.520333333333333,"lat":53.18111833333333,"raim":true,"radioStatus":"0100000100001110001","talkerId":"AI","sentenceId":"VDM","talkerId_text":"Mobile AIS station","sentenceId_text":"AIS VHF data-link message","messageType_text":"Position Report Class A","bearing":251.73879507739878,"distanceKM":2.9603108494349866,"distanceNM":1.5984399732661974,"cog":0,"originalMessage":"!AIVDM,1,1,,A,10=0rr?P00PIAB@NKS?h0?wP2PQi,0*6C"}]

[UnborN]

@Nice_to_have

How can I remove the undefined?

hello .. you want to remove the undefined and replace it with what ?

me for example, i have an application that reads from some energy meters and then writing to an sqlite database. If for any reason a meter is offline and its values are undefined i replace them with null to write to the db.

Example based on your sample data :

[{"id":"469914edff7b3f7e","type":"function","z":"54efb553244c241f","name":"","func":"\nlet mmsi = msg.payload[0].mmsi || null\nlet channel = msg.payload[0].channel || null\nlet messageType = msg.payload[0].messageType || null\nlet repeatIndicator = msg.payload[0].repeatIndicator || null\nlet navigationStatus = msg.payload[0].navigationStatus || null\nlet sog = msg.payload[0].sog || null\nlet positionAccurate = msg.payload[0].positionAccurate || null\nlet lon = msg.payload[0].lon || null\nlet lat = msg.payload[0].lat || null\nlet timeStamp = msg.payload[0].timeStamp || null\nlet raim = msg.payload[0].raim || null\nlet radioStatus = msg.payload[0].radioStatus || null\nlet talkerId = msg.payload[0].talkerId || null\nlet sentenceId = msg.payload[0].sentenceId || null\nlet talkerId_text = msg.payload[0].talkerId_text || null\nlet sentenceId_text = msg.payload[0].sentenceId_text || null\nlet messageType_text = msg.payload[0].messageType_text || null\nlet bearing = msg.payload[0].bearing || null\nlet distanceKM = msg.payload[0].distanceKM || null\nlet distanceNM = msg.payload[0].distanceNM || null\nlet version = msg.payload[0].version || null\nlet navigationStatus_text = msg.payload[0].navigationStatus_text || null\nlet shipId = msg.payload[0].shipId || null\nlet callsign = msg.payload[0].callsign || null\nlet name = msg.payload[0].name || null\nlet shipType = msg.payload[0].shipType || null\nlet dimensionToBow = msg.payload[0].dimensionToBow || null\nlet dimensionToStern = msg.payload[0].dimensionToStern || null\nlet dimensionToPort = msg.payload[0].dimensionToPort || null\nlet dimensionToStarboard = msg.payload[0].dimensionToStarboard || null\nlet fixType = msg.payload[0].fixType || null\nlet eta = msg.payload[0].eta || null\nlet draught = msg.payload[0].draught || null\nlet destination = msg.payload[0].destination || null\nlet dte = msg.payload[0].dte || null\nlet shipType_text = msg.payload[0].shipType_text || null\nlet fixType_text = msg.payload[0].fixType_text || null\nlet turningDirection = msg.payload[0].turningDirection || null\nlet cog = msg.payload[0].cog || null\nlet trueHeading = msg.payload[0].trueHeading || null\nlet turningDirection_text = msg.payload[0].turningDirection_text || null\nlet originalMessage = msg.payload[0].originalMessage || null\n\n\n\nmsg.topic = \"INSERT or REPLACE INTO AIS VALUES ( \" + mmsi + \",\" + channel + \", \" + messageType + \",\" + repeatIndicator + \",\" + navigationStatus + \",\" + sog + \",\" + positionAccurate + \",\" + lon + \", \" + lat + \",\" + timeStamp + \",\" + raim + \",\" + radioStatus + \",\" + talkerId + \",\" + sentenceId + \",\" + talkerId_text + \",\" + sentenceId_text + \",\" + messageType_text + \",\" + navigationStatus_text + \",\" + bearing + \",\" + distanceKM + \",\" + distanceNM + \",\" + version + \",\" + shipId + \",\" + callsign + \",\" + name + \",\" + shipType + \",\" + dimensionToBow + \",\" + dimensionToStern + \",\" + dimensionToPort + \",\" + dimensionToStarboard + \",\" + fixType + \",\" + eta + \",\" + draught + \",\" + destination + \",\" + dte + \",\" + shipType_text + \",\" + fixType_text + \",\" + turningDirection + \",\" + cog + \",\" + trueHeading + \",\" + turningDirection_text + \",\" + originalMessage + \")\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":700,"wires":[["0a89644b10df60b2"]]},{"id":"10a1c8052362fb08","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"channel\":\"A\",\"messageType\":1,\"mmsi\":\"013646568\",\"sog\":0,\"positionAccurate\":true,\"lon\":5.520333333333333,\"lat\":53.18111833333333,\"raim\":true,\"radioStatus\":\"0100000100001110001\",\"talkerId\":\"AI\",\"sentenceId\":\"VDM\",\"talkerId_text\":\"Mobile AIS station\",\"sentenceId_text\":\"AIS VHF data-link message\",\"messageType_text\":\"Position Report Class A\",\"bearing\":251.73879507739878,\"distanceKM\":2.9603108494349866,\"distanceNM\":1.5984399732661974,\"cog\":0,\"originalMessage\":\"!AIVDM,1,1,,A,10=0rr?P00PIAB@NKS?h0?wP2PQi,0*6C\"}]","payloadType":"json","x":290,"y":700,"wires":[["469914edff7b3f7e"]]},{"id":"0a89644b10df60b2","type":"debug","z":"54efb553244c241f","name":"db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":640,"y":700,"wires":[]}]

[hammoaj]

What do you want to be in your string instead of 'undefined'? I assume that you will need something there as that will correspond to a column in your database? Does that mean that you want undefined replaced with 'null'? You could just use <string>.replace(/undefined/g, "null")

[Nice_to_have]

The fields that are undefined in this message might be send in another message.

I want to use the database to collect as much info as I can about a certain vessel.

I will not use it like for example as a sensor.

It will be without a timestamp.

The messages I get update the existing fields, so when I query a vessel, I get the latest info.

So the undefined field should not be written to the database, as there might already be data in there.

[Nice_to_have]

@hammoaj

.replace(/undefined/g, "null")

could I do this?

<string>.replace(/undefined/g, "")

[UnborN]

I think you will need an SQL query a little more advanced, than just replacing an undefined string to empty or null.

with the above way you may be losing/replacing information for a vessel that you wrote in the db with previous INSERTS because you said :

@Nice_to_have

The fields that are undefined in this message might be send in another message.

and i guess the opposite is also a possibility : fields that have values in one msg could be undefined in the next msgs ?

There is a stackoverflow answer of an sql query that will do an UPDATE instead of INSERT (if the primary key already exists) but i have not idea how to combine that to also check if some column is null and update only that value.

[jbudd]

As long as you only add fields and values to your SQL insert or replace which are not undefined, it should be fine.
The missing fields cannot be defined as NOT NULL in the database.

I think that your example data does not have any undefined values?

You could loop through all the elements of the input object pushing field names to one array and values to another. Then assemble those into your query.

A sample query to insert or update:
INSERT INTO customer_data (customer_id, customer_name, customer_place)
VALUES(2, "Vaani","Denver") ON DUPLICATE KEY UPDATE customer_name = "Hevika", customer_place = "Denver";

[UnborN]

Based on @jbudd 's information

in a function node we can make the construction of the SQL dynamically

let columns = Object.keys(msg.payload[0]).join(",")

let values = Object.values(msg.payload[0]).map(el => typeof el === 'string' ? `'${el}'` : el)
values = values.join(",")

let mmsi = msg.payload[0].mmsi


msg.topic = `INSERT INTO AIS (${columns}) VALUES (${values}) ON DUPLICATE KEY UPDATE mmsi = ${mmsi}`

return msg;

Flow:

[{"id":"469914edff7b3f7e","type":"function","z":"54efb553244c241f","name":"","func":"let columns = Object.keys(msg.payload[0]).join(\",\")\n\nlet values = Object.values(msg.payload[0]).map(el => typeof el === 'string' ? `'${el}'` : el)\nvalues = values.join(\",\")\n\nlet mmsi = msg.payload[0].mmsi\n\n\nmsg.topic = `INSERT INTO AIS (${columns}) VALUES (${values}) ON DUPLICATE KEY UPDATE mmsi = ${mmsi}`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":700,"wires":[["0a89644b10df60b2"]]},{"id":"10a1c8052362fb08","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"channel\":\"A\",\"messageType\":1,\"mmsi\":\"013646568\",\"sog\":0,\"positionAccurate\":true,\"lon\":5.520333333333333,\"lat\":53.18111833333333,\"raim\":true,\"radioStatus\":\"0100000100001110001\",\"talkerId\":\"AI\",\"sentenceId\":\"VDM\",\"talkerId_text\":\"Mobile AIS station\",\"sentenceId_text\":\"AIS VHF data-link message\",\"messageType_text\":\"Position Report Class A\",\"bearing\":251.73879507739878,\"distanceKM\":2.9603108494349866,\"distanceNM\":1.5984399732661974,\"cog\":0,\"originalMessage\":\"!AIVDM,1,1,,A,10=0rr?P00PIAB@NKS?h0?wP2PQi,0*6C\"}]","payloadType":"json","x":210,"y":700,"wires":[["469914edff7b3f7e"]]},{"id":"0a89644b10df60b2","type":"debug","z":"54efb553244c241f","name":"db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":630,"y":700,"wires":[]}]

ps. is mmsi the primary key of your table ?

[Colin]

Is there a reason you are using a database for this? You could possibly instead use persistent context in node red, I think that might be much easier.

[jbudd]

Considering a database, I think all of the supplied fields have to be in the ON DUPLICATE KEY UPDATE section, with the exception of the unique key. Otherwise you never update fields that are null in the database.

Dynamically generating the query as above seems ideal but it only works if you have a db field for every datum that may arrive in a message.

Would something like this work? (pseudocode):

var DataWeWant = ["mmsi", "lat", "long", .....];
var fields = [];
var values = [];
var updates = [];
for dtype in DataWeWant {
  if input.dtype exists {
    fields.push(" ' " + dtype + " ' ,");
    values.push(input.dtype);
    updates.push(" ' " + dtype + " '  = " + input.dtype + ",")
  }
}

And then assemble the SQL from fields, values and updates, stripping off trailing commas.

I suspect Hiram Codd would be unhappy at cramming different sets of data into a single db record for each vessel. He might prefer a data table for each kind of message that could arrive, and only enforce uniqueness in a master VESSELS table. (It's been a long time since I designed databases!)