Replace NaN values with null to store in InfluxDB

General
1

Hello,

I'm looking for a function to replace all NaN values with null.

In my scenario I want to store Temp datainto an InfluxDB. If a Sensor doesn't work i will get NaN as a value. To store this missing information into InfluxDB i need a null instead of NaN.

image

2

Hi .. you can use isNaN() to check whether the values are numbers or not

For example if you want to check if a single msg.payload is a number
and using the shorthand ternary operator :

msg.payload = isNaN(msg.payload) ? null : msg.payload
return msg;

Test flow:

[{"id":"cf304722d2abbcda","type":"function","z":"54efb553244c241f","name":"function 2","func":"\nmsg.payload = isNaN(msg.payload) ? null : msg.payload\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":1560,"wires":[["ea5de6e5881acca8"]]},{"id":"b681fd287b399e73","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"text","payloadType":"str","x":250,"y":1520,"wires":[["cf304722d2abbcda"]]},{"id":"bc4f2422e6d994bf","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"32.6","payloadType":"num","x":250,"y":1600,"wires":[["cf304722d2abbcda"]]},{"id":"ea5de6e5881acca8","type":"debug","z":"54efb553244c241f","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":600,"y":1560,"wires":[]}]

In your case you have an array of a single object
it could be for the first property :

msg.payload = [
{
"FLW_Temp_EM300_S_01" : isNaN(msg.VarFLW_Temp_EM300_S_01) ? null : msg.VarFLW_Temp_EM300_S_01,
...
}
]

ps. its good in this cases to share the actual code in text form and using the image icon.

3

Are you sure that you want, for example
"FLM_etc", null
rather than just leaving that line out completely?

4

Hey UnborN,

thanks for your reply!
This way works very well!

5

Well i don't know maybe this could even better.

How would a function for this look like?

6

Something like this possibly (untested):

const keys = [
    "FLW_Temp_EM3000_S_01",
    "FLW_Temp_EM3000_S_02",
    // etc
]
msg.payload = [{}]
keys.forEach(function( key ) {
    const property = `Var${key}`
    if (!isNaN(msg[property])) {
        msg.payload[0][key] = msg[property]
    }
})
return msg;
7

Hi Guys,

I have been puzzling over a similar problem my side, also storing into Influx. My problem (I think) is slightly different though...

I have multiple inputs from various sensors, every now and then a few "skip a beat" and send NaN which the Influx node then gripes about:

ie:
screenshot-192.168.0.118_1880-2022.06.30-16_18_03

This is what one of the flows looks like:

screenshot-192.168.0.118_1880-2022.06.30-16_20_21
screenshot-192.168.0.118_1880-2022.06.30-16_20_21788×174 34.1 KB

And the content of the "preparatory node" ie UpstairsFridgeAuto is this:

msg.payload = {"UpstairsFridgeAuto":msg.payload};
return msg;

Is it possible to add a bit of code to the InfluxCheck node to filter out these pesky NaN errors without having to edit each individual preparatory node?

Regds
Ed

8 
if (isNan(msg.payload) {
  // set msg to null to stop the node sending anything
  msg = null
} else {
  msg.payload = {"UpstairsFridgeAuto":msg.payload};
}
return msg;
9

@Colin

Nope, while that will work, if you see above, I am needing to filter out the NaN one step further down the line in the next node... The InfluxCheck node per se' .....

By the time msg.payload hits that node, the msg.payload that would need to be "filtered" and substituted would be: "AnyOneOfMultipleMeasurements":NaN

TIA

Ed

10

Sorry, you have lost me. Show us the message going into the node where you want to filter it and tell us what you want to get out.

11

@Colin

Ok...

A correct message.payload is: {"UpstairsFridgeAuto":1} or {"BasementPumpAuto":1} or {"WhateverSensorIsReporting":1}

A "bad" message.payload is: {"UpstairsFridgeAuto":NaN} or {"BasementPumpAuto":NaN} or {"WhateverSensorIsReporting":NaN}

I am looking for a way to detect theNaN regardless of the "WhateverSensorIsReporting": bit and replace it with a value of my choice...

TIA

Ed

12

OK, understood. That isn't the way I would do it (I prefer to validate values as soon as possible rather than have bad values propagating through the flow). However, you can fetch the keys of an object using Object.keys(obj) So you could use something lke

if ( isNan(msg.payload[Object.keys(msg.payload)[0]]) ) {
  msg = null
} else {
  // otherwise not null so do whatever is required
}
return msg

I prefer code that is easy to understand when I come back in a years time and try to work out what is going on, so I would do it in the upstream nodes.

13

On second thoughts I would replace your function nodes with Change nodes that set the topic to the field name, then in the prepare for influx node, filter out NaN and create the object using the topic as the field name.

14

Where do the values come from before they get to this flow? Perhaps it can be done even earlier.

15

@Colin

Thanks for the pointers... I eventually opted for this:

screenshot-192.168.0.118_1880-2022.06.30-19_18_01

[
    {
        "id": "ac5b0135792eb85b",
        "type": "inject",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "abc",
        "payloadType": "str",
        "x": 780,
        "y": 4310,
        "wires": [
            [
                "cecac7e7e0f1f28d"
            ]
        ]
    },
    {
        "id": "cecac7e7e0f1f28d",
        "type": "function",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "WhateverSensor",
        "func": "msg.payload = {\"WhateverSensor\":msg.payload};\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 790,
        "y": 4270,
        "wires": [
            [
                "eddfe5b443bda757"
            ]
        ]
    },
    {
        "id": "77fdb2d2ea549fcd",
        "type": "inject",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "1",
        "payloadType": "num",
        "x": 780,
        "y": 4230,
        "wires": [
            [
                "cecac7e7e0f1f28d"
            ]
        ]
    },
    {
        "id": "eddfe5b443bda757",
        "type": "function",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "Filter NaN",
        "func": "if ( isNaN(msg.payload[Object.keys(msg.payload)[0]]) ) {\n  return [null, msg ];//is NaN - Send to 2nd Output\n} else {\n  return [ msg, null];// not NaN - Send to 1st Output\n}",
        "outputs": 2,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 960,
        "y": 4270,
        "wires": [
            [
                "974a2fa3b87ec1a4"
            ],
            [
                "b440126ed98a9843"
            ]
        ],
        "outputLabels": [
            "Valid",
            "Invalid"
        ]
    },
    {
        "id": "974a2fa3b87ec1a4",
        "type": "debug",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1055,
        "y": 4250,
        "wires": [],
        "l": false
    },
    {
        "id": "b440126ed98a9843",
        "type": "debug",
        "z": "357c3c56.dcf1d4",
        "g": "31f8eab0.d7a7be",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1055,
        "y": 4290,
        "wires": [],
        "l": false
    }
]

They originate from about 100+++ Sonoff switches, a couple of inverters and other misc kit... As they get replaced/upgraded/updated/changed/played with, the payloads (Objects) might change as needed... This is a final check and filter before the data gets packed away for graphical analyses... So, no... earlier is not really a requirement as, as such, it is a final check before filing...

Incidentally, the check for non NaN allows boolean through too... A little bonus I didn't expect, but suits my requirements fine!!

Sorry for hijacking the thread, but hopefully it is of use!!

Thanks again for the help and pointers, Colin!!

Cheerz
Ed

16

It isn't so much that it is a requirement, as the fact that it can make life easier.
For the Sonoff devices, are you picking them all up using wildcards? If so then you could do the test as soon as you get it from there, Though I can't remember ever seeing a NaN from a Sonoff. In fact I don't think NaN can be sent over MQTT.

Are you sure you have correctly determined how the NaN values are arising?

17

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.