# Replace NaN values with null to store in InfluxDB

**URL:** <https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494>\
**Category:** General\
**Created:** [29 June 2022 10:20 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494 "2022-06-29T10:20:46Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Easiii](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/easiii/32/63976_2.png) [@Easiii](https://discourse.nodered.org/u/Easiii)\
**Post date:** [29 June 2022 10:20 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/1 "2022-06-29T10:20:47Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/4/840e8100897a8be49fda33deef886c9aa621a500.png)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [29 June 2022 10:47 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/2 "2022-06-29T10:47:14Z")

</div>

Hi .. you can use [isNaN()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Conditional_Operator) :

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

```

**Test flow:**

```auto
[{"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 :

```auto
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/0/406ef58b7171502458876f9708d927729274866b.png) icon.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [29 June 2022 10:57 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/3 "2022-06-29T10:57:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Easiii](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/easiii/32/63976_2.png) [@Easiii](https://discourse.nodered.org/u/Easiii)\
**Post date:** [29 June 2022 11:15 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/4 "2022-06-29T11:15:29Z")

</div>

Hey UnborN,

thanks for your reply!  
This way works very well!

---

<div class="post-metadata">

**Author:** ![Easiii](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/easiii/32/63976_2.png) [@Easiii](https://discourse.nodered.org/u/Easiii)\
**Post date:** [29 June 2022 11:16 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/5 "2022-06-29T11:16:22Z")

</div>

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

How would a function for this look like?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [29 June 2022 20:25 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/6 "2022-06-29T20:25:47Z")

</div>

Something like this possibly (untested):

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![eddee54455](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/eddee54455/32/43350_2.png) [@eddee54455](https://discourse.nodered.org/u/eddee54455)\
**Post date:** [30 June 2022 14:27 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/7 "2022-06-30T14:27:23Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/8/e8b1b8e47b795abceadf0f0eaf245f164d087dd7.png)

This is what one of the flows looks like:

 ![screenshot-192.168.0.118_1880-2022.06.30-16_20_21](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/6/963151edbaf260ef4d20e316c4e9a813e10a5131.png)

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

```auto
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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 14:45 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/8 "2022-06-30T14:45:52Z")

</div>

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

```

---

<div class="post-metadata">

**Author:** ![eddee54455](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/eddee54455/32/43350_2.png) [@eddee54455](https://discourse.nodered.org/u/eddee54455)\
**Post date:** [30 June 2022 14:54 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/9 "2022-06-30T14:54:41Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 14:57 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/10 "2022-06-30T14:57:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![eddee54455](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/eddee54455/32/43350_2.png) [@eddee54455](https://discourse.nodered.org/u/eddee54455)\
**Post date:** [30 June 2022 15:06 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/11 "2022-06-30T15:06:41Z")

</div>

@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 the`NaN` regardless of the `"WhateverSensorIsReporting":` bit and replace it with a value of my choice...

TIA

Ed

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 15:12 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/12 "2022-06-30T15:12:25Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 15:34 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/13 "2022-06-30T15:34:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 15:51 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/14 "2022-06-30T15:51:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![eddee54455](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/eddee54455/32/43350_2.png) [@eddee54455](https://discourse.nodered.org/u/eddee54455)\
**Post date:** [30 June 2022 17:23 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/15 "2022-06-30T17:23:38Z")

</div>

@Colin

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

![screenshot-192.168.0.118_1880-2022.06.30-19_18_01](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/9/e9bfeadfc62962d397995e7119a122ecf3e75b70.png)

```auto
[
    {
        "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
    }
]

```

> [@Colin](#):
>
> Where do the values come from before they get to this flow? Perhaps it can be done even earlier.

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2022 19:13 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/16 "2022-06-30T19:13:27Z")

</div>

> [@eddee54455](#):
>
> So, no... earlier is not really a requirement

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?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [29 August 2022 19:13 UTC](https://discourse.nodered.org/t/replace-nan-values-with-null-to-store-in-influxdb/64494/17 "2022-08-29T19:13:59Z")

</div>

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