Efficient recipe for getting MQTT data into an InfluxDB database using Node-Red

MQTT_Node-Red_Influx.md

Recipe: from MQTT to InfluxDB via Node-Red

Introduction

Getting data produced by IoT sensors into a database is practically a mandatory step before effective visualisation (eg dashboards).

This recipe shows you how to get an MQTT payload into an InfluxDB database using three Node-Red nodes. It makes the following assumptions:

• A client device of some kind publishing data to a topic via the MQTT protocol;
• Mosquitto (MQTT broker);
• Node-Red subscribing to the topic; and
• InfluxDB running and accessible to Node-Red.

This recipe also show how to map between JSON keys and database field and tag names.

Task goal

Given this MQTT message structure:

  topic: /site/topic
message: {"b": true, "i": 123, "r": 456.78, "s": "hello world", "t": "tagValue"}

the task is to configure the Node-Red instance to:

1. Listen for the topic "/site/topic";
2. Parse the JSON-format message payload; and
3. Add a row to a measurement (table) named "example" in an InfluxDB database named "test".

This recipe also demonstrates how to use abbreviated keys in a JSON message and map those to more meaningful field names (attributes) in the InfluxDB database:

JSON Key	Influx Field	Influx Tag	Expected Value	Implied type
b	flag		true	Boolean
i	discrete		123	Integer
r	continuous		456.78	Real
s	message		"hello world"	String
t		identity	"tagValue"	String

There is nothing magical about either these JSON keys, or the InfluxDB field or tag names. JSON keys do not have to be single characters, and InfluxDB names do not have to be long, meaningful or descriptive. It is just that, in general, IoT devices are memory-constrained so short strings are to be preferred over longer strings; while "meaningful and descriptive" are considered best practice in a database environment.

There is also nothing magical about the choice of these implied data types. They just happen to be the ones you are most likely to need to pass between a client device and a database.

Preparation

client device (simulated)

During testing you will find it useful to have a computer with an MQTT client installed. Explaining the full how-to of this is beyond the scope of this recipe so you should start at mosquitto.org/download.

On Linux, it is not immediately obvious that you need either or both of the following:

$ sudo apt install mosquitto-clients
$ sudo apt install mosquitto

The first command installs the MQTT publishing and subscribing command-line clients. You will need this package to get the mosquitto_pub command.

The second command installs the MQTT broker. You will only need this if you do not have another MQTT broker running somewhere (eg in a Docker container).

InfluxDB

If your copy of InfluxDB is running inside a Docker container, consider adding this alias statement to your .profile or .bashrc:

$ alias influx='docker exec -it influxdb influx -precision=rfc3339'

That alias allows you to connect to the "influx" command line interface (CLI) simply by typing:

$ influx

By default, "influx" displays time as nanoseconds since 1970-01-01 UTC. The -precision=rfc3339 argument displays time in human-readable form.

The "test" database must be created by hand. If you omit this step you will get an error from Node-Red. You can initialise the database like this:

$ influx
> CREATE DATABASE test
> quit

Node-Red

• Launch your browser and connect to your Node-Red server.
• Use the main menu (three horizontal bars "≡" at the top, right of the Node-Red window) to open the Palette Manager:
  • select the "Nodes" tab and check whether node-red-contrib-influxdb is already installed. If it is not installed,
  • select the "Install" tab, then search for and install node-red-contrib-influxdb. If you prefer to install contributions from the command line, do that.

A three-node flow

Back in the Node-Red main window, click the "+" button to add a new empty flow. The default title will be something like "Flow 1". Double-click the title and rename the flow with something more descriptive, like "Influx Test". The actual name is not important.

1. "mqtt in" node

Drag an "mqtt in" node onto the canvas. Double-click to open and configure as follows:

1. Select the option to add a new mqtt-broker.
2. Click the pencil icon to open the server properties panel.
3. Give the server a meaningful name (eg "Docker MQTT").
4. Supply the network path to the host running Mosquitto:
   • In a Docker environment, this will be the name of the container running Mosquitto (eg "mosquitto").
   • In a non-Docker enviroment where Node-Red and Mosquitto are running on the same host, this will be the loopback address 127.0.0.1.
   • If Node-Red and Mosquitto are running on different hosts then this will be a static IP address or the fully-qualified domain name of the host running Mosquitto.
5. Click "Add".
6. Enter the topic string ("/site/topic").
7. Set the "Output" popup to "a parsed JSON object".
8. Enter a name for the node. This appears in the schematic and it is good practice to repeat the topic string.
9. Click "Done" to complete the node setup.

All other fields can either be left at their default settings or changed to suit your requirements.

The purpose of this node is to:

1. Listen to MQTT messages directed to "/site/topic"; and
2. Convert the JSON string in the MQTT message body to a JavaScript object representation.

In other words, given an input of the JSON string specified in the task goal, the output from the node will be:

msg.payload = {
    b: true,
    i: 123,
    r: 456.78,
    s: "hello world"
    t: "tagValue"
}

2. "change" node

Drag a "change" node onto the canvas.

Connect the outlet of the "mqtt in" node to the inlet of the "change" node.

Double-click the "change" node to open and configure as follows:

1. Enter a name for the node. This appears in the schematic and it is good practice to summarise the purpose of the node.

2. A new change node contains a single rule to "Set msg.payload" but where the data type of the "to" field defaults to a string. Change the popup menu to a Java expression.

3. Click the ellipsis ("…") to open the expression editor.

4. Copy the expression below and paste it into this window.

   [
       {
           "flag": msg.payload.b,
           "discrete": msg.payload.i,
           "continuous": msg.payload.r,
           "message": msg.payload.s
       },{
           "identity": msg.payload.t
       }
   ]
   

5. Click "Done".

6. Click "Done" to complete the node setup.

The purpose of this node is to provide a cross-walk between the JSON keys ("b", "i", "r", "s" and "t"), and the field and tag names you need in the InfluxDB database. The basic pattern is:

[
   {
      fieldName : msg.payload.key,
      ...
   },{
      tagName : msg.payload.key,
      ...
   }
]

If you only want to pass fields, then omit the square brackets and the elements that describe the tag(s), like this:

{
   fieldName : msg.payload.key,
   ...
}

Note that it is feasible to omit this "change" node entirely. If you do that the JSON keys in the Node-Red "payload" variable will become the field names in the database. Before you take that shortcut, consider:

• If an MQTT client starts providing unexpected JSON keys, those can easily pollute your InfluxDB database. Using a cross-walk between the expected JSON keys and the field and tag names you want in your database provides an effective barrier against unexpected keys.

• You may wish to include keys in your MQTT payload that you do not want to wind up in your database. Good examples are millis() uptime, free heap and version numbers. Such values are usually ephemeral and only of interest at the moment when they are produced (and might only be produced when you are actively debugging your MQTT client). You can always see such values by subscribing to the MQTT feed or attaching a Debug node to the "mqtt in" node.

• A "change" node simplifies the process of adding new tags and fields. You can:

  • Add a new key+value pair to the JSON payload being produced by your MQTT client, then
  • Attach a Debug node to the "mqtt in" node to confirm that you are receiving the expected data, then
  • Change the cross-walk when you are ready to start importing the data into your database.

• If you want to include both tags and fields in your database, you really only have two options, either:

  • your MQTT client has to format the JSON payload correctly before transmission, or
  • you need a "change" node to implement a cross-walk.

• Opting to do the work in your MQTT client effectively rules out the tactical use of ephemeral values and a step-wise refinement approach to development if you need to add new fields.

Given the output from the "mqtt in" node, the Javascript expression in the "change" node will result in:

msg.payload = [
   {
      flag: true,
      discrete: 123,
      continuous: 456.78,
      message: "hello world"
   },{
      identity: "tagValue"
   }
]

3. "influxdb out" node

Drag an "influxdb out" node onto the canvas.

Can't find the "influxdb out" node in the palette? Double-check that you installed "node-red-contrib-influxdb" as described above.

Connect the outlet of the "change" node to the inlet of the "influxdb out" node.

Double-click the "influxdb out" node to open and configure as follows:

1. From the "Server" popup menu, choose "Add new influxdb...".
2. Click the pencil icon to open the server properties panel.
3. Supply the network path to the host running InfluxDB:
   • In a Docker environment, this will be the name of the container running InfluxDB (eg "influxdb").
   • In a non-Docker enviroment where Node-Red and InfluxDB are running on the same host, this will be the loopback address 127.0.0.1.
   • If Node-Red and InfluxDB are running on different hosts then this will be a static IP address or the fully-qualified domain name of the host running InfluxDB.
4. The name of the InfluxDB database. This needs to be the same as you created earlier ("CREATE DATABASE test").
5. I recommend leaving this field blank. If you do then the "Server" field in the previous panel will take on the "host:port/database" appearance shown at the end of the dotted line in the figure. You lose that valuable synopsis by supplying a name in this field.
6. Click "Add".
7. Supply the name of the measurement you want to write to. This is analogous to a "table" in SQL parlance. The recommended name at this stage is "example".
8. Enter a name for the node. This appears in the schematic. It is good practice to summarise the purpose of the node.
9. Click "Done" to complete the node setup.

All other fields can be left at their default settings or changed to suit your requirements.

Warning: InfluxDB database connections are global to Node-Red. Suppose you have an existing flow connected to the "test" database. When you create a new flow for a new database, it is very tempting to copy the "influxdb out" node from the old flow, paste it into the new flow, open it, click the pencil icon, and just change the database name. If you do that, you will break your old flow because it will refer to the new database. Always start from scratch by dragging a new "influxdb out" node onto the canvas.

Given the output from the "change" node, the practical effect of this node is:

$ influx
> USE test
> INSERT example,identity=tagValue flag=true,discrete=123,continuous=456.78,message="hello world"
> quit

Saving your work

Click the Deploy button near the top, right of the canvas.

Testing your work

Add two "debug" nodes to the canvas. Double-click each in turn and set its Output to "complete msg object". Connect the outlet of the "mqtt in" node to the first "debug" node, and the outlet of the "change" node to the second "debug" node. The final result should look something like this:

Select the Debug panel (the controls below "Deploy").

Click "Deploy" to activate. Any errors will show up in the Debug panel.

Copy the following text to the clipboard then paste it into a text editor.

mosquitto_pub -h host -t '/site/topic' -m '{"b": true, "i": 123, "r": 456.78, "s": "hello world", "t": "tagValue"}'

Edit the "host" field to point to the server running your Mosquitto broker. This might be an IP address or a fully-qualified domain name.

Paste the text into a Terminal window on your client device and press return.

If all goes well, you will get two debug messages from Node-Red. The first is from the "mqtt in" node confirming receipt of the JSON payload:

▿ object
  topic: "/site/topic"
▿ payload: object
   b: true
   i: 123
   r: 456.78
   s: "hello world"
   t: "tagValue"

and the second is from the "change" node showing the effect of the cross-walk:

▿ object
  topic: "/site/topic"
▿ payload: array[2]
  ▿0: object
   flag: true
   discrete: 123
   continuous: 456.78
   message: "hello world"
  ▿1: object
   identity: "tagValue"

To confirm that the data made it all the way to the InfluxDB database:

$ influx
> USE test

> show measurements
name: measurements
name
----
example

> show series
key
---
example,identity=tagValue

> show tag keys
name: example
tagKey
------
identity

> show field keys
name: example
fieldKey   fieldType
--------   ---------
continuous float
discrete   float
flag       boolean
message    string

> select * from example
name: example
time                           continuous discrete flag identity message
----                           ---------- -------- ---- -------- -------
2020-02-12T03:56:07.844235334Z 456.78     123      true tagValue hello world

> quit

Cleaning up

You can either delete or deactivate the "debug" nodes in the Node-Red flow.

When you no longer need the test database, you can remove it like this:

$ influx
> DROP TABLE test
> quit

Hi Phill, this guide is amazing! Thanks to share your knowledge!!! I'm trying to get data from ShellyEM. I'm not a programmer, but I have a general understanding of MQTT, NodeRed and InfluxDB. Could you help me adjust the JSO Expression in the Change Node for ShellyEM? I'm need to get the following topics: Power, Reactive Power and Voltage. Thanks for your support!

I am doing something similar. A Hiking Class 1 electricity meter. It speaks RS485. I have an RS485-to-RS232 adapter going into an ESP8266. The sketch queries the meter and builds an MQTT payload.

In NodeRed, my "change" node looks like this:

{
    "voltage": msg.payload.v,
    "frequency": msg.payload.f,
    "current": msg.payload.a,
    "importEnergy": msg.payload.e,
    "meterErrors": msg.payload.eMask
}

In my case, volts, frequency, amps, accumulated kWh (those from the meter) plus an error mask from the Arduino. I'm about to re-implement the whole thing based on a better RS485-to-RS232 and an ESP32. The old RS485-to-RS232 is not very good and keeps losing connectivity - hence the error mask. There are two fields in the JSON payload (uptime and free heap) that I don't send into the database.

I only have one meter with no plans to do more so all of the above are "fields" in the Influx database - no tags - so there's only the one level of {..} needed.

The payload I work with looks like this:

There's no array structure in mine. Yours looks like it is an array nested inside another structure so you'll probably have to figure that out. Is your screen shot from Node-Red or something else? WIth Node-Red you can generally click the "copy path" button in the output from a Debug node and it makes a fair fist of providing you with exactly what you need.

Hi Phill, thanks for your help! Your implementation looks very nice!

I'm running MQTT Explorer (previews screenshot)

These are the topics to record on the DB:
TOPIC 1: shellies/shellyem-B9FXXX/emeter/0/power
TOPIC 2: shellies/shellyem-B9FXXX/emeter/0/reactive_power
TOPIC 3: shellies/shellyem-B9FXXX/emeter/0/voltage

In node red it looks like this (example for voltage):

This is the MQTT node in Node-Red:

This is the expression I have, but something is not define correctly:

[
    {
        "continuous": payload.voltage,
        "continuous": payload.power,
        "continuous": payload.reactive_power,
    }
]

Error messege I get when running in Node-Red:

OK. Several things.

First, the "cannot be used as a unary operator" is talking about the "," after "reactive_power". You need to get rid of that, as in:

[
    {
        "continuous": payload.voltage,
        "continuous": payload.power,
        "continuous": payload.reactive_power
    }
]

Second, if you are only going to have InfluxDB field names and no tag names, then you should also lose the array structure (the square brackets), as in:

{
    "continuous": payload.voltage,
    "continuous": payload.power,
    "continuous": payload.reactive_power
}

"field" and "tag" names - what's the difference? The best way to think of it is that fields carry variable data while tags carry invariant metadata. Suppose you had two electriciy meters named "house" and "garage". Those values would never change so they should become tags. Values like volts and power change all the time so those should be fields. You can ignore tags entirely and do everything with fields. It's just that, if it does make sense to describe something as a tag then InfluxDB can optimise how it is stored and be more efficient at query time. The question you should be asking yourself is whether you are ever likely to get a second electricity meter? If yes then something like "shellyem-B9FXXX" could be a candidate for a tag with a name like "meter". It's always easier to set up structures like this at the beginning than have to retrofit later.

Third, compare/contrast yours with mine:

{
    "voltage": msg.payload.v,
    "frequency": msg.payload.f,
    "current": msg.payload.a,
    "importEnergy": msg.payload.e,
    "meterErrors": msg.payload.eMask
}

See how my strings on the left hand side are all different? Those are the field names in the database. Here's some data from my database. It might help you make sense of the relationship between the Change node code and what turns up in the database:

$ influx
> use power
> select * from hiking where time > '2020-09-08T08:20:00+10:00' tz('Australia/Sydney')

name: hiking
time                                current frequency importEnergy meterErrors voltage
----                                ------- --------- ------------ ----------- -------
2020-09-08T08:20:03.642928912+10:00 0       50        512.94       0           245.4
2020-09-08T08:20:13.641139535+10:00 0       49.96     512.94       0           245.1
2020-09-08T08:20:23.642903797+10:00 0       50.01     512.94       0           244.9
2020-09-08T08:20:33.649072031+10:00 0       50.01     512.94       0           245.4
2020-09-08T08:20:43.644218934+10:00 0       50.04     512.94       0           245.6
2020-09-08T08:20:53.644731362+10:00 0       50.06     512.94       0           245.4
2020-09-08T08:21:03.643557176+10:00 0       50.05     512.94       0           245.3
> 

At best, your cross-walk is going to wind up with only a single value in each row called "continuous", or the InfluxDB Out node is going to get creative and call the fields "continuous", "continuous1", "continuous2". Your cross-walk really needs to be something like this:

{
    "voltage": payload.voltage,
    "power": payload.power,
    "reactive": payload.reactive_power
}

Fourth, I can't be 100% sure about this but I think it is unlikely that your device is sending each value as a separate topic. I think it is far more likely that it is sending one topic with all those values in a single payload. It would be highly inefficient to send three MQTT messages where one would do, and would make it extremely difficult to stitch time-related values back together to get them into the database.

The first part of what you wrote above implies three topics, each carrying a single value:

TOPIC 1: shellies/shellyem-B9FXXX/emeter/0/power
TOPIC 2: shellies/shellyem-B9FXXX/emeter/0/reactive_power
TOPIC 3: shellies/shellyem-B9FXXX/emeter/0/voltage

but your cross-walk structure implies a single topic with a payload containing multiple values.

This is what my MQTT messages look like:

2020-09-08T08:29:43+1000 /merle/HikingMonitor {"v":244.20,"f":49.94,"a":0.00,"e":512.94,"eMask":0,"upTime":54308,"heap":41680}
2020-09-08T08:29:53+1000 /merle/HikingMonitor {"v":243.90,"f":49.93,"a":0.00,"e":512.94,"eMask":0,"upTime":54318,"heap":41680}

The topic is "merle/HikingMonitor". The payload is everything in the curly braces.

Rather than relying on MQTT_Explorer, can I suggest using mosquitto_sub to see what is going on. It is an excellent tool for debugging. The reason I'm suggesting it is because I think MQTT_Explorer might be over-using the word "topic" and that could be misleading.

On a Mac, you get mosquitto, mosquitto_pub and mosquitto_sub via:

$ brew install mosquitto

On a Raspberry Pi, mosquitto_pub and mosquitto_sub are in their own package, so:

$ sudo apt install mosquitto-clients

Whatever operating system you are running, figure out how to get mosquitto_sub installed, then do:

$ mosquitto_sub -v -h «broker» -t "#" -F "%I %t %p"

replacing "«broker»" with the domain name or host name or IP address of the host running your MQTT broker. One of your earlier screen shots suggested it might be 192.168.100.109, so:

$ mosquitto_sub -v -h 192.168.100.109 -t "#" -F "%I %t %p"

Notice the format string on the right after the -F option. That is saying "show me the date+time, the topic, and the payload". You will then easily be able to figure out whether you have one topic or multiple topics. Once you know that, you will be in a better position to define the topic in your MQTT in node, then a debug node attached to that will show you how it is being parsed and that will help you set up the right hand side of the change node.

But, if you want, we can go step by step. Just get mosquitto_sub set up and send me a few lines of output.

Phill, thanks for your help! I follow your instrucciones and you are 100% correct, only one topic is being published:

Details from Shelly's documentation:

I try changing in the JSO Expression but something is missing,

[
    {
       "voltage": payload.voltage,
       "power": payload.power,
       "reactive": payload.reactive_power
    },{
        "identity": payload.shellyem-B9FXXX
    }
]

I get the following output in the debug window,

My objective is to assign tags, so that when I run queries inside grafana I can select unit of measure (e.g. W) and a sensor (e.g. shellyem-B9FXXX) ,

Do you think this is possible with MQTT and JSON? Or maybe you have other suggestions? Thanks!

OK. What that mosquitto_sub output means is that all the information is, indeed, being submitted as independent topics with exactly one value per topic.

Now that I think about it, this is also how my meter naturally provides its data.

To be fair to Hiking, the meter API has the ability to accept a query asking for multiple registers but I found that to be quite unreliable. Whether that's the meter itself, my RS232-to-RS485 adapter, my sketch, or my own misunderstanding of the API is an open question. What my Arduino sketch does is iterate around an array where each element in the array tells it what query to send to the meter and how to interpret the response. The queries are emitted at the rate of one every second so, if voltage was queried at t+0, it will next be queried at t+4. When the "send MQTT report" timer fires every 10 seconds, the payload will contain "the most recent results" which, logically, will be up to 4 seconds old. So, in reality, my data is not really time-aligned. It is "close enough" for my purposes. For example, I'm trying to correlate the voltage that the meter sees with what a solar inverter is producing on the DC and AC sides in kW. The inverter only reports every 5 minutes so "voltage" coming out of my meter being somewhere between 0 and 4 seconds away from the time shown in the database is neither here nor there.

As an aside, notice the green, blue and red shaded areas and associated horizontal dividers. The Australian Standard for voltage is 230V. It should spend 95% of its time in the green area with only occasional excursions into the blue area. It should never get into the red area. You can see is spends most of its time in the blue and regularly deviates into the red. That's the old 240V standard. The standard changed in 2003. Here we are 17 years later and "the system" still hasn't caught up. In the last couple of months, the distributors have been talking about using the ability to push voltage over 258V as a way of forcing all home inverters to trip offline. Never mind anything inside the home that has a transformer in it. Bizarre!

Here is what I would do with your MQTT feeds. Because there are 7 distinct topics of which you only want three (power, reactive_power and voltage), I suggest three "three node flows" in parallel on the same sheet. It's simple, easy to understand, and even slightly more efficient than trying to do it with wild-cards. Three "MQTT in" nodes, subscribing:

shellies/shellyem-B9Fxxx/emeter/0/power
shellies/shellyem-B9Fxxx/emeter/0/reactive_power
shellies/shellyem-B9Fxxx/emeter/0/voltage

Three change nodes:

[
    {
       "voltage": payload.voltage
     },{
       "identity": payload.shellyem-B9FXXX
    }
]

[
    {
      "power": payload.power
    },{
       "identity": payload.shellyem-B9FXXX
    }
]

[
    {
       "reactive": payload.reactive_power
    },{
        "identity": payload.shellyem-B9FXXX
    }
]

Three "InfluxDB out" nodes, where the database is always called, say, "shelley" but where the measurement is set to "voltage" or "power" or "reactive" as appropriate.

At the CLI, you'd be doing queries like:

> use shelley
> select * from voltage

and getting back rows with time, identity and voltage headers. Something like Grafana is not going to care about this. If you try to display all three on the same chart (say power and reactive on the left hand Y axis and voltage on the right hand Y axis), it is simply going to plot the observations where they fall. It'd still be three queries, irrespective of whether all the data is in one measurement or in three.

Apologies if I'm stating the blindingly obvious here.

After that was working, you might then want to go back and match on wildcards for the meter name:

shellies/#/emeter/0/power
shellies/#/emeter/0/reactive_power
shellies/#/emeter/0/voltage

Then, insert another change node between each "MQTT in" and cross-walk "Change" node called something like "extract identity" where the instruction was to Set "msg.identity" to the Javascript expression "$split(msg.topic,'/')[1]". And then you could change each of the cross-walk "Change" nodes like this:

[
    {
       "voltage": payload.voltage
     },{
       "identity": msg.identity
    }
]

You might be able to figure out how to do that without the extra change nodes but I could never get it to work, which is why I do it in two steps.

I'm doing something quite similar to the above for weather sensors:

Hi Phill, I think I'm almost there! This is how my flow looks,

MQTT Node is working perfect as well as the Extract node. I'm only having trouble with the main topic, it is not passing to the array.

This is the JSO expression,

[
   {
       "voltage": payload.voltage
    },
   {
       "identity": msg.identity
    }
]

I get an empty value in the first element of the array. Any ideas why?

Thanks for your help!!

I may have misled you on the wildcard. Sorry. The correct symbol is "+" which is for a single level while '#' is for multiple levels. The "#" is correct for mosquitto_sub as in:

mosquitto_sub -v -h iot-hub -t "shellies/#" -F "%I %t %p" &

just not for matching topics in flows.

Assuming mosquitto_pub installed, I can simulate your data like this:

mosquitto_pub -h iot-hub -t "shellies/shellyem-B9Fxxx/emeter/0/power" -m 306.07
mosquitto_pub -h iot-hub -t "shellies/shellyem-B9Fxxx/emeter/0/reactive_power" -m 368.51
mosquitto_pub -h iot-hub -t "shellies/shellyem-B9Fxxx/emeter/0/voltage" -m 225.32

iot-hub is the name of my RPi running Mosquitto (the broker).

A four-node flow that listens for "power":

The debug output from the "Prepare influx insert" node:

During that journey, I also discovered something strange. The gist has this:

[
    {
        "flag": payload.b,
        "discrete": payload.i,
        "continuous": payload.r,
        "message": payload.s
    },{
        "identity": payload.t
    }
]

and I know that worked because I got it going before I wrote the gist; what's in the gist is copy-and-paste.

But my flow for my actual electricity meter that works day in, day out, is:

{
    "voltage": msg.payload.v,
    "frequency": msg.payload.f,
    "current": msg.payload.a,
    "importEnergy": msg.payload.e,
    "meterErrors": msg.payload.eMask
}

Only fields (no tags). But that's not the odd bit. The odd bit is the "msg." prefix on all of those.

Turns out that most of my flows have a "msg." prefix on the right hand side. But here's another working (day in, day out) flow which doesn't do that:

[
    {
        "temp": payload.temp
    },{
        "system": payload.host
    }
]

The fact that that flow is working suggests "msg." is implied but I could not get the example for you to work unless I used the "msg." prefix. That might be the problem you were having too. I really don't know whether to change the gist or not. I'll have to do some more testing.

But, putting that to one side, this is the code that I used to make it work:

[
   {
       "power": msg.payload
   },
   {
       "identity": msg.identity
   }
]

Why not "msg.payload.power" or some variation on that theme? Because it isn't passed as a key-value JSON string. It's just passed as the value.

A-n-y-w-a-y, if you select all of the text in the box below and copy to the clipboard, you should then be able to Main Menu > Import > paste into a new flow. You will then have a working example to duplicate.

[
    {
        "id": "4eeaad15.e8ad0c",
        "type": "tab",
        "label": "Test",
        "disabled": false,
        "info": ""
    },
    {
        "id": "64532868.ef5248",
        "type": "mqtt in",
        "z": "4eeaad15.e8ad0c",
        "name": "shellies/+/emeter/0/power",
        "topic": "shellies/+/emeter/0/power",
        "qos": "2",
        "datatype": "json",
        "broker": "d93117e3.d7b6e",
        "x": 150,
        "y": 80,
        "wires": [
            [
                "650772a0.54ecac"
            ]
        ]
    },
    {
        "id": "650772a0.54ecac",
        "type": "change",
        "z": "4eeaad15.e8ad0c",
        "name": "extract identity",
        "rules": [
            {
                "t": "set",
                "p": "identity",
                "pt": "msg",
                "to": "$split(msg.topic,'/')[1]",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 380,
        "y": 80,
        "wires": [
            [
                "227d1df3.ab106a"
            ]
        ]
    },
    {
        "id": "227d1df3.ab106a",
        "type": "change",
        "z": "4eeaad15.e8ad0c",
        "name": "Prepare influx insert",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "[\t   {\t       \"power\": msg.payload\t   },\t   {\t       \"identity\": msg.identity\t   }\t]\t",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 600,
        "y": 80,
        "wires": [
            [
                "38349f13.0725a"
            ]
        ]
    },
    {
        "id": "38349f13.0725a",
        "type": "debug",
        "z": "4eeaad15.e8ad0c",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 790,
        "y": 80,
        "wires": []
    },
    {
        "id": "d93117e3.d7b6e",
        "type": "mqtt-broker",
        "z": "",
        "name": "Merle MQTT",
        "broker": "mosquitto",
        "port": "1883",
        "clientid": "",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthRetain": "false",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closeRetain": "false",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willRetain": "false",
        "willPayload": ""
    }
]

Hi Phill! Thanks again for your help! Everything work out perfect!!! This is the end result:

Brilliant! Right now my Hiking (class 1 electricity meter) is in a box connected to a power point. It really only sees voltage and frequency because there's nothing connected on the output side, so that's all the ESP8266 project connected to it via an RS485 adapter sees. That project "works" but I had to add a lot of code to detect problems - which I ultimately traced to the RS485 adapter.

I have a duplicate meter that was installed in the house distribution board by an electrician which will see whole-of-house including whatever the solar system pushes back into the grid. I also have a new ESP32 project at breadboard stage sitting on my desk, using a new kind of RS485 adapter which is far more reliable. When I get some time ... I'll be able to follow your lead. 🤓

Thank you Phill for writing up these topics. Not nearly done, but getting dangerous real fast! mqtt to nodered to influx to grafana is working! Thanks!

Hello
i have a couple of question but the first "problem" i just get all the Message from 433MHz.
so i would like to use a Switch to tell unknown and known device
and the splitt the known device by ID. At this time would be just two known device
could you tell how to do this splitting?

have a nice day
vinc

I need to see some actual data. See the earlier discussion above about setting up the mosquitto clients and running the mosquitto_sub command to capture the MQTT packets arriving at your broker. Collect a couple of lines showing (a) the known device and (b) an unknown device, and I will see if I can work it out. I don't need pages of data. I only need one MQTT message from each device.

Hello
thanks for your time

This are 3 example, both Oregon i know them, other not but still ok just to know it is there.
The time is 1h behind too.
The ID i am not sure where give this Number, but it will change as soon i have to change the battery - i just have tested it.

i would say a way to go

switch Message
case id = 126 go out 1
case id = 16 go out 2
case id = other go out 3

out 1 and 2 should then go to infulxDB
out 1 and 2 check Battery_Ok if 0 then send telegram

ok this time it worked to switch it

[{"id":"256b14f0.81a1dc","type":"mqtt in","z":"95ec0171.e3383","name":"","topic":"/iot12/433MHz","qos":"0","datatype":"json","broker":"fdd3c090.0e24","x":150,"y":300,"wires":[["92eda324.b8c89","c1745410.f9e998"]]},{"id":"c1745410.f9e998","type":"switch","z":"95ec0171.e3383","name":"switch by ID","property":"payload.id","propertyType":"msg","rules":[{"t":"eq","v":"246","vt":"str"},{"t":"eq","v":"126","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":3,"x":420,"y":320,"wires":[["db2d768a.165bc8"],["f5f2994b.a68ff8"],["d4ba2e5f.1a8cd"]]},{"id":"db2d768a.165bc8","type":"debug","z":"95ec0171.e3383","name":"","active":false,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":700,"y":280,"wires":[]},{"id":"f5f2994b.a68ff8","type":"debug","z":"95ec0171.e3383","name":"","active":false,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":700,"y":360,"wires":[]},{"id":"d4ba2e5f.1a8cd","type":"debug","z":"95ec0171.e3383","name":"","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":700,"y":440,"wires":[]},{"id":"fdd3c090.0e24","type":"mqtt-broker","name":"localHost - iot1","broker":"10.18.42.175","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

========================================
18.12.2020, 18:10:08 node: RAW msg.payload
/iot12/433MHz : msg.payload : Object
object
time: "2020-12-18 17:10:09"
brand: "OS"
model: "Oregon-THGR122N"
id: 126
channel: 2
battery_ok: 1
temperature_C: 6.8
humidity: 73

---

18.12.2020, 18:10:18node: RAW msg.payload
/iot12/433MHz : msg.payload : Object
object
time: "2020-12-18 17:10:19"
brand: "OS"
model: "Oregon-THGR122N"
id: 16
channel: 1
battery_ok: 1
temperature_C: 21.1
humidity: 40

---

18.12.2020, 18:12:16 node: RAW msg.payload
/iot12/433MHz : msg.payload : Object
object
time: "2020-12-18 17:12:16"
model: "TFA-Pool"
id: 142
channel: 1
battery_ok: 1
temperature_C: 8.1
mic: "CHECKSUM"

---

When I received the email copy of your last post, it didn't have your flow as part of it so I went ahead and created a test flow to your specs. I was about to paste that here, then I saw your flow so I pasted your flow into my Node-Red.

Our solutions were identical, save that you specified IDs 126 and 16, while your flow tests for 246 and 126. I mention that because I don't see 246 in your test data while I do see 16.

Oh, I edited your comment to wrap three back-ticks around your JSON. For future reference (not just if you post something here but elsewhere too), it makes flows more human-friendly if you do two things:

1. When you export from Node-Red, turn on the "formatted" option in the "Export nodes" dialog. That gives you a well-formed output spanning a number of lines (instead of a single line that scrolls off to infinity); and
2. Use the triple back-ticks before and after the paste so that markdown format makes it clear where the JSON begins and ends.

Here's your flow implementing those recommendations:

[
    {
        "id": "c1745410.f9e998",
        "type": "switch",
        "z": "a1af091f.5b352",
        "name": "switch by ID",
        "property": "payload.id",
        "propertyType": "msg",
        "rules": [
            {
                "t": "eq",
                "v": "246",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "126",
                "vt": "str"
            },
            {
                "t": "else"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 3,
        "x": 430,
        "y": 260,
        "wires": [
            [
                "db2d768a.165bc8"
            ],
            [
                "f5f2994b.a68ff8"
            ],
            [
                "d4ba2e5f.1a8cd"
            ]
        ]
    }
]

Anyway, I'm glad that you solved the problem. Node-Red is a steep learning curve, isn't it? But it's fun too.

Yes i am happy it start to work like a wish, but still many problems to solve ;-)
the ID change when changing the Battery on the device - i have tested it this is ther reason the ID change.

to bring it no to Telegram is another chalange...
on the video and description it looks easy but then just small things change and i am blocked!

have a nice day keep healty
vinc

Hi,
Thanks for the excellent explanation.
I've just learned from your gist what a measurement is (I was using DBs as single-table).
I'm already able to insert information in the influxDB via node red, but I was wondering if there would be any best-practice do to it.

I'm having issues trying to create the grafana dashboards and therefore suspecting that maybe I'm inserting the values as string while maybe it should be as integer/floats, so this might the the opportunity to (re)make all as it should be.

Anyway, I have a couple of questions I'd like to ask your kind opinion:

1. Are the mentioned json keys (b, i, r, s, t) an industry best practice (and therefore found on IoT devices) or are they your personal implementation/recommendation?
2. Why the change node implements an array with two objects? Ie. Any reason why "identity" is separated?
3. For the case where there are several sensors (temperature/humidity) around the house, what would be the DB best-practices?
   One table (measurement) for each device? / The same table for all temperature devices and another for all humidity ones? / Any other setup?
4. For logging boolean sensors (like PIR and magnetic sensors) is a database like influxdb also recommended?

Thanks,
Joaoabs

Hi Phill! Thanks again for your help! Everything work out perfect!!! This is the end result:

Wow, that looks really great. Is that done by grafana?
If so, would you be able to share how you did that please?

I already have an influx db being populated with power information, but I'm not being able to build such a dashboard.
I believe I'd have in influxdb all the information required to build it (example printout below), just need some directions to build the dashboard.

Thanks,
Joaoabs

> use power
Using database power
> select * from power
name: power
time                activeaparentpower current exportactiveenergy importactiveenergy power   powerfactor reactiveaparentpower totalactiveenergy voltage
----                ------------------ ------- ------------------ ------------------ -----   ----------- -------------------- ----------------- -------
1609635431945968365 2769.65            11.27   252806             22743666           2765.10 1.00        -157.10              22996472          246.90
1609635731940326138 2806.40            11.72   252806             22743890           2788.90 0.99        -312.10              22996698          240.80
1609636031946486351 2537.83            10.74   252806             22744110           2530.20 1.00        -195.30              22996916          236.90
1609636331958220821 779.58             3.47    252806             22744242           747.80  0.96        -220.00              22997048          238.00
1609636631959526265 1497.89            6.13    252806             22744352           1466.30 0.98        -305.70              22997158          245.60
1609636931959220801 1650.54            6.68    252806             22744486           1644.20 1.00        -144.20              22997292          251.40

@jabss

Are the mentioned json keys (b, i, r, s, t) an industry best practice (and therefore found on IoT devices) or are they your personal implementation/recommendation?

Neither. They are just example names, corresponding with:

• "b" for boolean (ie typical values are "true" and "false")
• "i" for integer (ie typical values are whole numbers)
• "r" for real (ie typical values include decimal points)
• "s" for string (ie anything "encapsulated in quotes")
• "t" also a string but intended to become a tag

Here are some examples of payload data coming from two of my own sensors:

{"v":244.60,"f":49.99,"a":0.00,"e":512.94,"eMask":0,"upTime":42928,"heap":41680}
{"v":246.0,"ac":300,"day":1.3,"sum":43254.5,"grid":-8,"p1":328,"p2":381,"batt":68}

You'll see I use a mixture of single-character keys and longer keys:

• The first payload comes from an electricity meter. The values are volts, frequency, amps, accumulated kWh, an error mask, the uptime of the Arduino doing the work, and the free heap on the Arduino. The last three never get into any database but it's useful to see them when I subscribe to MQTT traffic using mosquitto_sub.
• The second payload comes from a solar inverter. The values are volts, AC watts being generated at the moment (it's very cloudy), the accumulated kWh generated today, the running sum of kWh generated by this inverter since installed, watts to/from the grid (negative means from grid - like I said very cloudy), p1 and p2 are the DC watts being generated by the two strings of solar panels, and batt is the charge state of the batteries (68% full).

There's really no hard and fast rule for any of this. If you want the field key in your database to be "volts" there's no reason why you should use "v" or "volts" or "voltage" in the MQTT JSON payload. I offered "memory constraints" in an IoT device as one reason for using abbreviations rather than full names. Another is that shorter payloads are better when I'm using mosquitto_sub to monitor things (which I do quite a lot - my first debugging step if I think something might be wrong somewhere). Even if you were 100% consistent about JSON keys being equal to database keys, there are still good reasons to use a cross-walk, including:

• filtering out things like "eMask", "uptime" and so on from the above,
• the ease with which you can split into fields and tags.
• doing things like splitting values from a single payload across different databases. An example is a battery-powered temperature sensor. You want to track the voltage as it runs down and the depletion curve is probably of some interest so you can predict when it is likely to run out but "vBat" doesn't really belong with temperature or humidity, plus you've also probably got a mixture of sensors, some battery powered, some not. Two separate paths through two separate cross-walks leading to two separate InfluxDB-out nodes is a better answer.

Why the change node implements an array with two objects? Ie. Any reason why "identity" is separated?

Yes. Because "identity" is a tag. Influx makes a distinction between "fields" and "tags". Suppose you have three temperature sensors. You've got one "outside", another in your "kitchen" and the third is in your "dining room". You might be seeing MQTT flows like this:

/home/temperature/outside {"temp": 19.6}
/home/temperature/kitchen {"temp": 22.6}
/home/temperature/diningRoom {"temp": 21.8}

Alternatively, it might be arranged like this:

/home/temperature {"location": "outside", "temp": 19.6}
/home/temperature {"location": "kitchen", "temp": 22.6}
/home/temperature {"location": "diningRoom", "temp": 21.8}

The incoming format really doesn't matter. When you prepare this data for insertion into Influx, you have two choices:

• You can store everything as "fields", or
• You can store variable data as "fields" and invariant data as "tags".

In this example, the temperatures are always going to be changing. You're interested in both the current measurement (what's the temperature in the kitchen now?), the differences over time (is it getting warmer or cooler outside?) and historical information (what was today's maximum temperature outside?).

Conversely, unless you move a sensor, the locations (outside, kitchen, diningRoom) are never going to change. The location-name is always the same on every MQTT message. It's invariant and a candidate for a tag.

Even if you do move a sensor (eg relocate the kitchen sensor to a bedroom), it might be the same physical device but it's a different logical device giving you a new data series, so it really needs a new name. You can't ask "is the temperature in the bedroom hotter than this time last year?" if the sensor was in the kitchen this time last year. Well, you can ask. But it's not a sensible question and it's better if your database returns a NaN or other error than an answer which is meaningless. Garbage in, garbage out!

So, why store something as a tag when a field will do the same job? It's about efficiency. If you define something as a tag, it is only stored once. The value is implied for every row in the series. It still behaves like a field for queries. In something like:

SELECT * FROM temperature WHERE location = 'kitchen'

it matters not at all whether location is a tag or a field. But, as your database grows and grows, that query is a lot more efficient if location is a tag, than if it is a field.

I suppose another way to think of it is like a sub-select in SQL:

SELECT min(temp), max(temp) FROM (SELECT temp FROM temperature WHERE location = 'kitchen')

In that expression, the query engine would create a temporary result set containing just temperature values for kitchen, then the outer query would look for the min() and max() values in that result set. My impression is that Influx tags essentially pre-split the data so the "sub selects" are an implicit part of the database. Another SQL analogue might be that you are indexing by location.

I don't know any of that for hard fact. I'm just guessing from what I see as I fiddle about.

A word to the wise:

• right back at the get-go I did not understand tags. I've been using SQL for a long time and I couldn't immediately see the point of tags so I shoved the issue to back of mind. I suffered a lot of pain putting everything right once the penny finally dropped. Please try to avoid making the same mistake. If you're thinking something like, "I still don't really understand, I'll stick with fields for now" then my advice is to take the time to understand it now.

For the case where there are several sensors (temperature/humidity) around the house, what would be the DB best-practices? One table (measurement) for each device? / The same table for all temperature devices and another for all humidity ones? / Any other setup?

Probably:

• one database called something like "weather"
• one measurement for each distinct metric:
  • temperature
  • humidity
  • pressure
  • etc
• one series for each contributing sensor - still using the example from above:
  • temperature,location=outside
  • temperature,location=kitchen
  • temperature,location=diningRoom

By the way, suppose you embed the location in the topic:

/home/temperature/outside

and you want to get access to "outside", the way I do it is to use a "change" node immediately after the MQTT-in node:

• Set msg.location
• to the Javascript expression $split(msg.topic,'/')[3]

Not "msg.payload.location", just "msg.location" because we don't want to interfere with the payload.

Using "3" as the index might seem counter-intuitive but it's correct. The string is "split" about the "/" so you wind up with:

• Index 0 = null string
• Index 1 = home
• Index 2 = temperature
• Index 3 = outside

Then, in the change node you use to set up the Influx insert, you'd do something like this:

[
    {
        "temp": msg.payload.temp
    },{
        "location": msg.location
    }
]

I tried doing the $split in the above but couldn't get it to work for some reason so I went with a separate change node. That's turned out to be handy because you can then more easily "switch" if you want to do things like "if outside falls below 5° then send me an alert".

For logging boolean sensors (like PIR and magnetic sensors) is a database like influxdb also recommended?

Influx is a time-series database. Time is the primary key of every table. While there may be situations where you are only interested in "now", it seems to me that I'm always asking questions like, "when was the door with that trigboard last opened?" or "has the postie opened the mailbox today to deliver something?" To me, it seems natural to log every event.

Although I haven't used them, I believe Influx's "retention policies" help with the question of "when is data so old as to be useless?". For example, if I think that I am never going to be interested in whether the mailbox was opened more than a week ago, I could set a retention policy to discard events more than one week old.

I'm having issues trying to create the grafana dashboards and therefore suspecting that maybe I'm inserting the values as string while maybe it should be as integer/floats, so this might the the opportunity to (re)make all as it should be.

I don't know. I suppose it's possible. Have you listed what Influx thinks are the data types? Here's an example:

$ influx
> USE weather
> SHOW FIELD KEYS

name: humidity
fieldKey fieldType
-------- ---------
humidity float

name: pressure
fieldKey         fieldType
--------         ---------
localPressure    float
seaLevelPressure float
trend            string

name: rainfall
fieldKey        fieldType
--------        ---------
rainGaugeClicks float

name: temperature
fieldKey fieldType
-------- ---------
temp     float

name: wind
fieldKey  fieldType
--------  ---------
compass   string
gustSpeed float
heading   float
windSpeed float

You can also do a "SHOW TAG KEYS" but it doesn't really tell you much because I'm pretty sure tags are always strings.

In Grafana, you might try the old programmer's trick of multiplying by 1.0. In many cases, even if "temp" was a string instead of a float, something like:

SELECT 1.0 * temp from temperature

would force an expression evaluation. If "temp" didn't evaluate to a number then it would be an implementation dependency as to whether the expression evaluated as zero, a NaN or chucked an error. If you're going to try this from the CLI, also stick a limit on the end, as in:

SELECT 1.0 * temp from temperature limit 5

You could be certain it was working by changing the coefficient to 2.0 and verifying that the numbers doubled.

Wow, that looks really great. Is that done by grafana?
If so, would you be able to share how you did that please?

@jabss - that isn't mine - you'd have to ask @ignaciosala

Mine are a bit simpler. This integrates 5-minute data from my solar inverter with 10-second data from probing an electricity meter:

The meter is attached to a wall plug so it's only good for voltage and frequency. I had an electrician install an identical meter in the distribution board so I could measure whole of house but I haven't gotten around to using that yet. When I do it'll probably be a dashboard more like @ignaciosala

My main interest with the original implementation was tracking the ghastly mains power foisted on us. Australia adopted 230V in 2003. Our power should always be in the green band with very few excursions into the blue band. As you can see, it's the other way around because the suppliers are more interested in maximising profits than expending $ changing transformer taps, and nobody in government has got the guts to do something simple and obvious like say, "if power spends more than 5% of its time in the blue band during any metering period, you can't charge for electricity for the whole of that metering period." That'd wise-'em-up something chronic. There'd be electricians in every street fixing the taps quicker than you could say "what electricity bill?".

We also get days so far into the red zone it's amazing appliances don't just up and melt:

Rooftop solar gets blamed for this but, strangely enough, the worst power spikes we ever had were all on the same day:

time                          voltage
----                          -------
2018-05-20T02:18:36.105+10:00 300
2018-05-20T02:18:46.097+10:00 300.4
2018-05-20T07:16:55.562+10:00 300.9
2018-05-20T07:17:05.559+10:00 300.1

Not only did that blow off the top of my chart but any fool can see the sun ain't shining at 2am and, given May 20th is practically the start of winter in the Southern Hemisphere, the sun ain't heaving a whole lot of high-energy photons in our direction at 7.15 in the morning.

This is my weather thing. Sensors all over the joint gathering temperature, humidity and pressure. Most are ESP8266 or 32. A couple are battery powered with WhisperNodes (AtMega328p chips + LoRa radio + exceptionally efficient power utilisation). I just stuck a weather station kit in the back yard (replaced the 433Mhz guts with a WhisperNode) so I'm getting rain & wind but I have to figure out how to shoehorn wind into this (there doesn't seem to be a Grafana widget for a compass rose).

The particulate charts come from a PurpleAir. Those speak HTTP rather than MQTT but it was easy enough to figure out how to get the data into Influx.

I think the most likely explanation is that you didn't set the output of the MQTT in node to be a "parsed JSON object". It is the 7th dot point of https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f#1-mqtt-in-node Can you double-check? 🛫

…

On 14 Jul 2021, at 01:37, Numex106 ***@***.***> wrote: ***@***.*** commented on this gist. Hi @Paraphraser Thanks for the excellent write up. I am having an issue where after parsing JSON, the message does not split out topic/payload. This is what your output after the mqtt in looks like ▿ object topic: "/site/topic" ▿ payload: object b: true i: 123 r: 456.78 s: "hello world" t: "tagValue" When I input the following. mosquitto_pub -h localhost -t '/SDG30/test' -m '{"b": true, "i": 123, "r": 456.78, "s": "hello world", "t": "tagValue"}' I get just an object object b: true i: 123 r: 456.78 s: "hello world" t: "tagValue" Is there an issue with how I've set up mosquitto, or do you know how I can still access the values as it is not inside the msg.payload anymore? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

@Paraphraser
I had two silly issues that are both fixed and it's working perfectly now, thanks!

1. I forgot to select "complete msg object" in the debug node, leading to my initial question.
2. I also didn't realize I had a pesky space at the end of my msg payload keys causing the change node to come up empty.

Thanks again for the fantastic write-up.

Thanks for the write-up! This was very helpful for a newb.

(I did have to add a JSON node in between to get things to work right.)

@NewHopeApiary you probably missed a step when you first set up the MQTT-in node. If you look at "7" in the diagram you'll see the output needs to be set to "parsed JSON object".

Your way isn't "wrong" so there's no need to change it unless you want to. The result is exactly the same as you are getting by using the extra JSON node. It's just that "MQTT-in to JSON to rest of flow" is such a common pattern that the MQTT-in node lets you do it in a single step. Make sense?

You are correct of course. :) I already had built out a flow and was just modifying it, so I missed that. Appreciate the reply, really wasn't expecting it. Again, thanks for sharing your knowledge!

Hello @Paraphraser, Thanks a lot for your commitment and effort on this! This has been helping me a lot.
I'm facing a problem where i have 5 Weather Stations (IDs = WS1,WS2,WS3,WS4,WS5) sending readings from simple sensors.
I want to feed the InfluxDB with them and have a single dashboard page for each of them. This project could escalate more so i'm having troubles how to build a flow that could handle and differentiate the IDs so it can send the data for the correct Gauges and Database.

I saw above that you provided a simple flow in which you extract the Sensor ID and prepare the Influx Insert. Could you Explain a little more how can i Write the Change Node JSON for that? I'm not experienced with this language, to be honest. But it does seems to be a simple solution.

Basically, if the station identifier (WS1, WS2) etc is carried in the message payload, you can use it directly. The "Prepare influx insert" Change node is "set msg.payload to the JSON expression", where the expression field is something like:

[
    {
        "temperature": msg.payload.temp
    },{
        "station": msg.payload.station
    }
]

remembering that this double-array structure tells the Influx insert node that temperature is a field, and station is a tag.

However, if the station identifier is carried in the topic string like this:

/home/weatherstation/WS1

it seems that you need to use a separate Change node ahead of the "Prepare influx insert". I usually call that separate Change node "extract station". It is set up as "set msg.station to the JSON expression" where the expression field is something like:

$split(msg.topic,'/')[3]

Then, the expression in the "Prepare influx insert" node becomes:

[
    {
        "temperature": msg.payload.temp
    },{
        "station": msg.station
    }
]

I assume you see what's going on in:

$split(msg.topic,'/')[3]

It's saying "crack the topic string about the slash". If you use a leading slash on your topic (as in this example of "/home/weatherstation/WS1") you wind up with four elements in the array where the zeroth element is a null string so element 1 is "home" and element 3 is "WS1". If you don't use a leading slash (and opinions vary on whether you should or shouldn't) then a topic like "home/weatherstation/WS1" would see you aiming at element [2] for WS1. Make sense?

I also assume you can see that the "extract station" node is only setting the field "msg.station" and isn't interfering with "msg.payload", so msg.payload persists unchanged into the "Prepare influx insert" node where "msg.station" is also then available.

Last point. The MQTT in node should be a wildcard match. It should be looking for:

/home/weatherstation/+

Of course, it doesn't really matter where the station is:

/home/+/weatherstation/

will match on "/home/WS1/weatherstation". If the device reports against topics like this:

/home/WS1/weatherstation/temp
/home/WS1/weatherstation/humidity

then you'll probably be running two flows, one for temperature, one for humidity, with the temperature flow matching on:

/home/+/weatherstation/temp

I'm really making the point that you can adapt to whatever the sensor provides.

I hope that all makes sense.

---

I really do not understand why the whole thing can't be done in a single Change node. Something like:

[
    {
        "temperature": msg.payload.temp
    },{
        "station": $split(msg.topic,'/')[3]
    }
]

I tried a few variations on that theme but Node-RED always either grizzled about syntax errors or accepted the expression as valid syntax but it then didn't work. I gave up and took it to be a rule that I needed two nodes.

If you, or anyone else reading this, can nut-out how to do it all in a single node, please point me in the right direction. I use this double-Change-node pattern all over the place and it would be great if it could be reduced to a single Change node.

@Paraphraser, Just a tip-of-the-hat "Thank You" to your gist. Your explanations of MQTT-relevant Node-Red nodes and answers to several questions has been extremely helpful. I especially value your tip about taking the time to figure out what you may not fully understand about InFlux DBs prior to savings lots of data. This caused me to re-configure my data flows into ones that are now more efficient. My only advice to others is to read (and re-read) this gist carefully because there are lots of details included here and it may take a few reads to fully comprehend all the subtle details.

OMG this is an excellent description :)

After finding this, it was very easy to get it to actually work :)

Thanks