Skip to content

Instantly share code, notes, and snippets.

@Paraphraser

Paraphraser/MQTT_Node-Red_Influx.md

Last active June 2, 2025 20:31
Show Gist options
  • Save Paraphraser/c9db25d131dd4c09848ffb353b69038f to your computer and use it in GitHub Desktop.
Save Paraphraser/c9db25d131dd4c09848ffb353b69038f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Paraphraser revised this gist May 17, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    # Recipe: from MQTT to InfluxDB via Node-RED

    * 2023-05-16 adds case study explaining how to extract values from topic strings, and how to specify the measurement name in the message payload provided to the "influxdb out" node.
    * 2024-05-16 adds case study explaining how to extract values from topic strings, and how to specify the measurement name in the message payload provided to the "influxdb out" node.
    * 2023-12-02 revise graphics to correspond with "influxdb out" node, and explain the pros and cons of InfluxDB 1.8 vs InfluxDB 2.

    ## Introduction
  2. Paraphraser revised this gist May 17, 2024. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -486,7 +486,7 @@ Creating a three-node flow for the temperature topic involves:
    10. Type the name "temperature". This will become the measurement name in your InfluxDB database.
    11. At the bottom, left of the "Edit change node" panel is a "+ add" button. Click it **twice**. This adds two more default rules.
    12. Replace "payload" with "device" (all lower case).
    13. Open the "to the value" popup menu and choose to a "Java expression".
    13. From the "to the value" popup menu, choose "Java expression".
    14. Enter the following into the expression field:

    ```
    @@ -525,7 +525,7 @@ Creating a three-node flow for the temperature topic involves:
    ```
    15. We are about to prepare the measurement for insertion into InfluxDB. In this case, the default receiver of `msg.payload` is correct for this rule.
    16. Open the "to the value" popup menu and choose to a "Java expression" and then click the ellipsis (<kbd><sup>...</sup></kbd>) button at the end of the field to open the "JSONata Expression editor".
    16. From the "to the value" popup menu, choose "Java expression", and then click the ellipsis (<kbd><sup>...</sup></kbd>) button at the end of the field to open the "JSONata Expression editor".
    17. Enter the following cross-walk expression into the editor window:
    ```
  3. Paraphraser revised this gist May 17, 2024. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -176,14 +176,14 @@ 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:
    In other words, given an input of the JSON string specified in the task goal, the output from the node will result in `msg.payload` containing:

    ``` json
    msg.payload = {
    {
    "b": true,
    "i": 123,
    "r": 456.78,
    "s": "hello world"
    "s": "hello world",
    "t": "tagValue"
    }
    ```
    @@ -207,7 +207,7 @@ Double-click the "change" node to open and configure as follows:
    3. Click the ellipsis ("…") to open the expression editor.
    4. Copy the expression below and paste it into this window.

    ``` json
    ```
    [
    {
    "flag": msg.payload.b,
    @@ -260,10 +260,10 @@ Note that it _is_ feasible to omit this "change" node entirely. If you do that t

    * 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:
    Given the output from the "mqtt in" node, the Javascript expression in the "change" node will result in `msg.payload` having the value:

    ``` json
    msg.payload = [
    [
    {
    "flag": true,
    "discrete": 123,
    @@ -528,7 +528,7 @@ Creating a three-node flow for the temperature topic involves:
    16. Open the "to the value" popup menu and choose to a "Java expression" and then click the ellipsis (<kbd><sup>...</sup></kbd>) button at the end of the field to open the "JSONata Expression editor".
    17. Enter the following cross-walk expression into the editor window:
    ``` json
    ```
    [
    {
    "temp" : msg.payload.observation
    @@ -559,7 +559,7 @@ Creating a three-node flow for the temperature topic involves:
    ``` console
    > CREATE DATABASE myweather
    ```
    See [InfluxDB](#create-database) for a step-by-step example.
    23. Leave the measurement field empty. It is being set in the "change" node (steps 8, 9 and 10).
  4. Paraphraser revised this gist May 17, 2024. 1 changed file with 10 additions and 10 deletions.
    20 changes: 10 additions & 10 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -180,11 +180,11 @@ In other words, given an input of the JSON string specified in the task goal, th

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

    @@ -265,12 +265,12 @@ Given the output from the "mqtt in" node, the Javascript expression in the "chan
    ``` json
    msg.payload = [
    {
    flag: true,
    discrete: 123,
    continuous: 456.78,
    message: "hello world"
    "flag": true,
    "discrete": 123,
    "continuous": 456.78,
    "message": "hello world"
    },{
    identity: "tagValue"
    "identity": "tagValue"
    }
    ]
    ```
  5. Paraphraser revised this gist May 17, 2024. 1 changed file with 289 additions and 40 deletions.
    329 changes: 289 additions & 40 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,17 +1,18 @@
    # Recipe: from MQTT to InfluxDB via Node-Red
    # Recipe: from MQTT to InfluxDB via Node-RED

    * 2023-12-02 revise graphics to correspond with InfluxDB-in node, and explain the pros and cons of InfluxDB&nbsp;1.8 vs InfluxDB&nbsp;2.
    * 2023-05-16 adds case study explaining how to extract values from topic strings, and how to specify the measurement name in the message payload provided to the "influxdb out" node.
    * 2023-12-02 revise graphics to correspond with "influxdb out" node, and explain the pros and cons of InfluxDB&nbsp;1.8 vs InfluxDB&nbsp;2.

    ## 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:
    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*.
    * *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.

    @@ -75,7 +76,7 @@ Given this MQTT message structure:
    message: {"b": true, "i": 123, "r": 456.78, "s": "hello world", "t": "tagValue"}
    ```

    the task is to configure the *Node-Red* instance to:
    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
    @@ -103,7 +104,7 @@ During testing you will find it useful to have a computer with an MQTT client in

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

    ```
    ``` console
    $ sudo apt install mosquitto-clients
    $ sudo apt install mosquitto
    ```
    @@ -112,41 +113,43 @@ The first command installs the MQTT publishing and subscribing command-line clie

    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).

    <a name="create-database"></a>
    ### InfluxDB

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

    ```
    ``` console
    $ 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:

    ```
    ``` console
    $ 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:
    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:

    ```
    ``` console
    $ influx
    > CREATE DATABASE test
    > quit
    ```

    ### Node-Red
    ### 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](https://nodered.org/docs/user-guide/runtime/adding-nodes):
    * 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](https://nodered.org/docs/user-guide/runtime/adding-nodes):
    * 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](https://flows.nodered.org/node/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.
    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.

    <a name="configure-mqtt-in-node"></a>
    ### 1. "mqtt in" node

    Drag an "mqtt in" node onto the canvas. Double-click to open and configure as follows:
    @@ -158,8 +161,8 @@ Drag an "mqtt in" node onto the canvas. Double-click to open and configure as fo
    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* environment 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*.
    * In a non-*Docker* environment 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".
    @@ -175,7 +178,7 @@ The purpose of this node is to:

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

    ```
    ``` json
    msg.payload = {
    b: true,
    i: 123,
    @@ -200,11 +203,11 @@ Double-click the "change" node to open and configure as follows:
    ![change-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-change-node.png)

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

    ```
    ``` json
    [
    {
    "flag": msg.payload.b,
    @@ -220,15 +223,15 @@ Double-click the "change" node to open and configure as follows:
    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:
    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 <a name="influx-insert-pattern"></a>basic pattern is:

    ```
    [
    {
    fieldName : msg.payload.key,
    "fieldName" : msg.payload.key,
    ...
    },{
    tagName : msg.payload.key,
    "tagName" : msg.payload.key,
    ...
    }
    ]
    @@ -238,18 +241,18 @@ If you only want to pass fields, then omit the square brackets and the elements

    ```
    {
    fieldName : msg.payload.key,
    "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:
    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.
    * 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
    * 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
    @@ -259,7 +262,7 @@ Note that it _is_ feasible to omit this "change" node entirely. If you do that t

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

    ```
    ``` json
    msg.payload = [
    {
    flag: true,
    @@ -272,6 +275,7 @@ msg.payload = [
    ]
    ```

    <a name="configure-influx-out-node"></a>
    ### 3. "influxdb out" node

    Drag an "influxdb out" node onto the canvas.
    @@ -291,20 +295,20 @@ Double-click the "influxdb out" node to open and configure as follows:
    5. Set the Version popup to "1.x" (this gist does not cover InfluxDB&nbsp;2; please see [Some words about InfluxDB](#aboutinflux) if you want to understand why).
    6. 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* environment 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*.
    * In a non-*Docker* environment 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*.
    7. The name of the *InfluxDB* database. This needs to be the same as you created earlier ("CREATE DATABASE test").
    8. Click "Add".
    9. Supply the name of the measurement you want to write to. This is analogous to a "table" in SQL parlance. The recommended name for this tutorial is "example".
    10. 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.
    > 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:

    ```
    ``` console
    $ influx
    > USE test
    > INSERT example,identity=tagValue flag=true,discrete=123,continuous=456.78,message="hello world"
    @@ -319,23 +323,23 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/a43181ad22cbf8a3f0630a311fe80a1e0a456e3b/mqtt-node-red-influx-test-flow.png)
    ![Node-RED test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/a43181ad22cbf8a3f0630a311fe80a1e0a456e3b/mqtt-node-red-influx-test-flow.png)

    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"}'
    ``` console
    $ 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:
    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
    @@ -365,7 +369,7 @@ and the second is from the "change" node showing the effect of the cross-walk:

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

    ```
    ``` console
    $ influx
    > USE test

    @@ -404,14 +408,259 @@ time continuous discrete flag identity message
    > quit
    ```

    ### Cleaning up
    ## Cleaning up

    You can either delete or deactivate the "debug" nodes in the *Node-Red* flow.
    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:

    ```
    ``` console
    $ influx
    > DROP DATABASE test
    > quit
    ```

    ## Case study

    Small case studies can be a useful way of exploring some slightly more advanced techniques which you may wish to use.

    Suppose you are the owner of a (fictional) "WeatherPro" weather station. The device reports its measurements like this:

    ```
    topic: weatherpro/F63617A7/temperature
    message: {"observation": 27.3, "units": "C"}
    topic: weatherpro/F63617A7/humidity
    message: {"observation": 85.6, "units": "%"}
    ```

    In each topic string:

    * `weatherpro` represents the name the manufacturer gave to the device;
    * `F63617A7` is the WeatherPro's serial number; and
    * `temperature` and `humidity` indicate the kind of data that is contained in the message payload.

    Within each message payload, the value of the measurement (temperature or humidity) is reported against the "observation" key word with "units" as metadata.

    Key point:

    * The `F63617A7` serial number is **not** repeated in the message payload. I am going to use this to show you how to extract values from the topic string.

    This case study will focus on the "temperature" topic but the same principles apply to the "humidity" topic, which is left as a homework exercise.

    ![case study configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/557ccffa8ffb0e23eafd49ebfd9254c1a343d4eb/mqtt-node-red-influx-influxdb-case.png)

    Creating a three-node flow for the temperature topic involves:

    1. Drag an "mqtt in" node onto the canvas and open it for editing.
    2. Configure your broker. Refer to [1. "mqtt in" node](#configure-mqtt-in-node") for the details on how to do that.
    3. We *could* subscribe to the topic:

    ```
    weatherpro/F63617A7/temperature
    ```

    However, is *preferable* to future-proof the design to some extent by using a wildcard in the serial number position:

    ```
    weatherpro/+/temperature
    ```

    In MQTT parlance, a:

    - `+` substitutes for exactly one position in the topic string; while
    - `#` substitutes for multiple positions. with the proviso that it must occur at the end of a topic string.

    4. Set the Output to "a parsed JSON object".
    5. Click "Done".
    6. Drag a "change" node onto the canvas and open it for editing.
    7. Give it a meaningful title. Examples:

    - "prepare observation"
    - "prepare temperature insert"

    The actual name is not important. It just needs to be meaningful to you.

    8. The default rule is "Set msg.payload to an empty string". Replace "payload" with "measurement" (all lower case). The reason for this will become clear in a while.
    9. The default type of the "to the value" popup is <sup>a</sup><sub>z</sub> (indicating a simple string). A string is what you want for this rule so leave it alone.
    10. Type the name "temperature". This will become the measurement name in your InfluxDB database.
    11. At the bottom, left of the "Edit change node" panel is a "+ add" button. Click it **twice**. This adds two more default rules.
    12. Replace "payload" with "device" (all lower case).
    13. Open the "to the value" popup menu and choose to a "Java expression".
    14. Enter the following into the expression field:

    ```
    $split(msg.topic,'/')[1]
    ```
    This decomposes to:
    - `$split(msg.topic,'/')` which means "find all the `/` characters in the topic string and use those as the separators to convert the topic string to an array":
    - Index position 0 = `weatherpro`
    - Index position 1 = `F63617A7`
    - Index position 2 = `temperature`
    - `[1]` means "select the value at index position 1" and return it as the result of the expression. Index position 1 is the serial number component of the topic string.
    Note:
    * If your topic string starts with a `/`, it implies that the first element in the array is a null string. For example, using the `$split()` function on this topic string:
    ```
    /weatherpro/F63617A7/temperature
    ```
    would yield:
    - Index position 0 = a null string
    - Index position 1 = `weatherpro`
    - Index position 2 = `F63617A7`
    - Index position 3 = `temperature`
    in which case the expression needed to select the serial number would be:
    ```
    $split(msg.topic,'/')[2]
    ```
    15. We are about to prepare the measurement for insertion into InfluxDB. In this case, the default receiver of `msg.payload` is correct for this rule.
    16. Open the "to the value" popup menu and choose to a "Java expression" and then click the ellipsis (<kbd><sup>...</sup></kbd>) button at the end of the field to open the "JSONata Expression editor".
    17. Enter the following cross-walk expression into the editor window:
    ``` json
    [
    {
    "temp" : msg.payload.observation
    },{
    "device" : msg.device
    }
    ]
    ```
    The [InfluxDB insert pattern](#influx-insert-pattern) was explained earlier. In this case:
    * the **field** named "temp" is being populated from the value associated with the "observation" keyword in the MQTT payload; while
    * the **tag** named "device" is being populated from the `msg.device` property, which was set by the second rule in the "change" node.
    Notes:
    * Mapping `observation` to `temp` demonstrates the ability of a cross-walk to let you use field or tag names in your database which are not present in the original MQTT messages; and
    * Ignoring `msg.payload.units` demonstrates the ability of a cross-walk to discard non-useful data.
    > What constitutes *useful* data depends on your needs and perspectives so your milage may vary when it comes to discarding temperature units. That said, units are *metadata* so, if you're going to retain them, you should use a *tag* rather than a *field*.
    18. Click "Done".
    19. Click "Done".
    20. Drag an "influxdb out" node onto the canvas and open it for editing.
    21. Give the node a meaningful name.
    22. Either choose an existing database connection from the popup or click the pencil icon and create a new connection. Refer to [3. "influxdb out" node](#configure-influx-out-node") for the details on how to do that. In this case, note that the database name is "myweather" and recall that it is necessary to create this database *before* use via the Influx command line:
    ``` console
    > CREATE DATABASE myweather
    ```
    See [InfluxDB](#create-database) for a step-by-step example.
    23. Leave the measurement field empty. It is being set in the "change" node (steps 8, 9 and 10).
    24. I've checked "Advanced Query Options" in the screen shot to make the point that it is possible to set the retention policy here. The alternative is to add another rule to the "change" node and use it to set a value for `msg.retentionPolicy` (the name is case sensitive). Not specifying a retention policy at all results in the default policy for the database being applied (typically `autogen`) and that this is usually what you want.
    25. Click "Done".
    26. Wire-up the three nodes as shown and click "Deploy".
    Notes:
    * Setting `msg.measurement` (steps 8, 9 and 10) while leaving the Measurement field (23) empty means you can use a single "influxdb out" node for a group of related topics (eg `weatherpro/F63617A7/humidity`).
    * The word "device" at step 12 and its use on both sides of the cross-walk expression (17) is not significant. You could also use "station" or something equally meaningful. It should be self-evident that you need to avoid keywords that are already in use (eg `payload`, `topic`, `measurement`, `retentionPolicy`).
    * It may seem tempting to use the `$split()` function to extract `temperature` and `humidity` from the topic string but, in practice, the cross-walk expression (17) will almost always be topic-specific so this is rarely useful in practice.
    ### Testing
    Run the following commands:
    ``` console
    $ mosquitto_pub -h mqtt.your.domain.com -t 'weatherpro/F63617A7/temperature' -m '{"observation": 27.3, "units": "C"}'
    ```

    > `mqtt.your.domain.com` could also be the host name or IP address of your broker (`-h` defaults to `localhost` and can be omitted if your broker is running on the same host).
    A "debug" node attached to the "mqtt in" node reports:

    ```
    topic: "weatherpro/F63617A7/temperature"
    payload: object
    observation: 27.3
    units: "C"
    qos: 0
    retain: false
    ```

    A "debug" node attached to the cross-walk ("Change" node) reports:

    ```
    topic: "weatherpro/F63617A7/temperature"
    payload: array[2]
    0: object
    temp: 27.3
    1: object
    device: "F63617A7"
    qos: 0
    retain: false
    measurement: "temperature"
    device: "F63617A7"
    ```

    Note:

    * `F63617A7` in the `payload` field was copied from the `device` field, which was first extracted from the `topic` field by the `$split()` function.

    Exploration within the `influx` command line interface reveals:

    ``` console
    $ influx

    > USE myweather

    > SHOW measurements
    name: measurements
    name
    ----
    temperature

    > SHOW series
    key
    ---
    temperature,device=F63617A7

    > SELECT * FROM temperature tz('Australia/Sydney')
    name: temperature
    time device temp
    ---- ------ ----
    2024-05-16T15:54:04.2839275+10:00 F63617A7 27.3

    > exit
    $
    ```

    ### homework

    Now, extend the basic three-node flow to subscribe to the "humidity" topic and write its observations to the `humidity` measurement in the **same** database.

    Hint:

    * You should only need two more nodes, not three.

    You can test your solution using:

    ``` console
    $ mosquitto_pub -h mqtt.your.domain.com -t 'weatherpro/F63617A7/humidity' -m '{"observation": 85.6, "units": "%"}'
    ```

    ### cleaning up

    Once you have finished with the database:

    ``` console
    $ influx
    > DROP DATABASE myweather
    > quit
    ```
  6. Paraphraser revised this gist Feb 21, 2024. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -282,7 +282,7 @@ 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:

    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/07e6b167d518a4499281160d98877c5a03773d7b/mqtt-node-red-influx-influxdb-out-node.png)
    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/ad7d15ccd77201578f5032386e921346a5526789/mqtt-node-red-influx-influxdb-out-node.png)

    1. Enter a name for the node. This appears in the schematic. It is good practice to summarise the purpose of the node.
    2. From the "Server" popup menu, choose "Add new influxdb...".
    @@ -412,6 +412,6 @@ When you no longer need the test database, you can remove it like this:

    ```
    $ influx
    > DROP TABLE test
    > DROP DATABASE test
    > quit
    ```
  7. Paraphraser revised this gist Dec 1, 2023. 1 changed file with 71 additions and 13 deletions.
    84 changes: 71 additions & 13 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,7 @@
    # Recipe: from MQTT to InfluxDB via Node-Red

    * 2023-12-02 revise graphics to correspond with InfluxDB-in node, and explain the pros and cons of InfluxDB&nbsp;1.8 vs InfluxDB&nbsp;2.

    ## Introduction

    Getting data produced by IoT sensors into a database is practically a mandatory step before effective visualisation (eg dashboards).
    @@ -13,6 +15,57 @@ This recipe shows you how to get an MQTT payload into an *InfluxDB* database usi

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

    <a name="aboutinflux"></a>
    ## Some words about InfluxDB

    At the time of writing (December 2023), InfluxData supports both InfluxDB&nbsp;1.8 and InfluxDB&nbsp;2.

    > InfluxDB&nbsp;3 has been announced but is not yet generally available.
    If you are beginning your IoT journey, you may wonder which version you should choose. Absent useful guidance, you may well reason like this:

    > Version 2.0 is obviously later than 1.8 therefore 2.0 must be "better".
    In some ways, InfluxDB&nbsp;2 can be seen as a natural progression from InfluxDB&nbsp;1.8. In other ways, the two database systems are like chalk and cheese. From a user perspective, one of the most obvious differences between the two systems is the query language:

    * InfluxDB&nbsp;1.8 uses *InfluxQL* natively with *Flux* being an experimental add-on which needs to be enabled;
    * InfluxDB&nbsp;2 uses *Flux* natively with partial support for *InfluxQL*.

    One consequence of the difference in query language affects Grafana. Its GUI has native point-and-click support for constructing InfluxQL queries that will run against InfluxDB&nbsp;1.8 but has no equivalent support for constructing Flux queries that run against InfluxDB&nbsp;2.

    There is a workaround. It involves using the InfluxDB&nbsp;2 web GUI to construct queries in Flux, which you then copy and paste into Grafana. This definitely works but the lack of *native* support for Flux in Grafana means that, if anything goes wrong, you often have to start from scratch.

    This workaround may seem like an acceptable compromise but, eventually, you will encounter situations where you will want to manipulate the data in your databases. Common examples are:

    * deleting junk that crept in when a sensor went berserk; and
    * reformatting, splitting or merging tables to better reflect how your sensors operate and/or to fix poor design decisions (we all make those).

    To manipulate your data, you will need to use the query language.

    If you are familiar with SQL then you will likely be comfortable with InfluxQL. While InfluxQL is not the same as SQL and does have its own peculiarities, it has sufficient syntactic similarities with SQL that you can usually figure out how to make it do what you need it to do. Plus, if you get stuck, you can generally find a close example by Googling and go from there.

    Flux, on the other hand, is its own language which … how can I put this politely … defies description. In my experience the official documentation is quite short on useful examples and Googling mostly fares no better.

    To put this problem more succinctly: you are far more likely to be able to get help with InfluxQL than you are with Flux. Whether that matters is something only you will know.

    The last point is that InfluxData has announced InfluxDB&nbsp;3. The announcement includes:

    1. This statement which appears in a popup overlay in the [Flux](https://docs.influxdata.com/flux/v0/) documentation:

    > The future of Flux
    >
    > Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

    2. This statement which appears in the [InfluxDB Clustered](https://docs.influxdata.com/influxdb/clustered/#influxdb-30) documentation:

    > … brings with it native SQL support and improved InfluxQL performance.

    I take those two statements to mean that Flux is a technological dead-end and, because of its dependence on Flux, so too is InfluxDB&nbsp;2.

    If you have a good reason for preferring InfluxDB&nbsp;2 then it's your system and your rules. But please don't say you weren't warned.

    This remainder of this gist focuses on InfluxDB&nbsp;1.8 exclusively.

    ## Task goal

    Given this MQTT message structure:
    @@ -40,7 +93,7 @@ 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.
    There is also nothing magical about the choice of these implied data types. Booleans, numbers (both integers and reals) and strings are just what you usually need to pass between a client device and a database.

    ## Preparation

    @@ -105,7 +158,7 @@ Drag an "mqtt in" node onto the canvas. Double-click to open and configure as fo
    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.
    * In a non-*Docker* environment 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").
    @@ -132,6 +185,10 @@ msg.payload = {
    }
    ```

    #### tip - avoiding a common mistake

    One common mistake is skipping step 7 above. Please go back and double-check that you have set the "Output" popup to "a parsed JSON object".

    ### 2. "change" node

    Drag a "change" node onto the canvas.
    @@ -225,20 +282,21 @@ 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:

    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-influxdb-out-node.png)
    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/07e6b167d518a4499281160d98877c5a03773d7b/mqtt-node-red-influx-influxdb-out-node.png)

    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*:
    1. Enter a name for the node. This appears in the schematic. It is good practice to summarise the purpose of the node.
    2. From the "Server" popup menu, choose "Add new influxdb...".
    3. Click the pencil icon to open the server properties panel.
    4. I recommend leaving the "Name" 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.
    5. Set the Version popup to "1.x" (this gist does not cover InfluxDB&nbsp;2; please see [Some words about InfluxDB](#aboutinflux) if you want to understand why).
    6. 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.
    * In a non-*Docker* environment 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.
    7. The name of the *InfluxDB* database. This needs to be the same as you created earlier ("CREATE DATABASE test").
    8. Click "Add".
    9. Supply the name of the measurement you want to write to. This is analogous to a "table" in SQL parlance. The recommended name for this tutorial is "example".
    10. Click "Done" to complete the node setup.

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

  8. Paraphraser revised this gist Aug 3, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -61,7 +61,7 @@ The second command installs the MQTT broker. You will only need this if you do n

    ### InfluxDB

    If your copy of *InfluxDB* is running inside a *Docker* container, consider adding this *alias* statement to your .profile:
    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'
  9. Paraphraser revised this gist Sep 3, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -261,7 +261,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-test-flow.png)
    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/a43181ad22cbf8a3f0630a311fe80a1e0a456e3b/mqtt-node-red-influx-test-flow.png)

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

  10. Paraphraser revised this gist Sep 3, 2022. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -98,7 +98,7 @@ Back in the *Node-Red* main window, click the "+" button to add a new empty flow

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

    ![mqtt-in-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-mqtt-in-node.png)
    ![mqtt-in-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-mqtt-in-node.png)

    1. Select the option to add a new mqtt-broker.
    2. Click the pencil icon to open the server properties panel.
    @@ -261,7 +261,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-mqtt-in-node.png)
    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-test-flow.png)

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

  11. Paraphraser revised this gist Sep 3, 2022. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -109,7 +109,7 @@ Drag an "mqtt in" node onto the canvas. Double-click to open and configure as fo
    * 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").
    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.

    @@ -140,7 +140,7 @@ 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:

    ![change-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-change-node.png)
    ![change-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-change-node.png)

    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.
    @@ -225,7 +225,7 @@ 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:

    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-influxdb-out-node.png)
    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-influxdb-out-node.png)

    1. From the "Server" popup menu, choose "Add new influxdb...".
    2. Click the pencil icon to open the server properties panel.
    @@ -261,7 +261,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-test-flow.png)
    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/0e559e4ee97697ed7dba14aba27f17d8a1df6526/mqtt-node-red-influx-mqtt-in-node.png)

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

  12. Paraphraser revised this gist Jan 2, 2021. 1 changed file with 16 additions and 15 deletions.
    31 changes: 16 additions & 15 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -146,23 +146,22 @@ Double-click the "change" node to open and configure as follows:
    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.
    5. Click "Done".
    6. Click "Done" to complete the node setup.

    The expression at step 4 is:
    ```
    [
    {
    "flag": msg.payload.b,
    "discrete": msg.payload.i,
    "continuous": msg.payload.r,
    "message": msg.payload.s
    },{
    "identity": msg.payload.t
    }
    ]
    ```

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

    @@ -243,6 +242,8 @@ Double-click the "influxdb out" node to open and configure as follows:

    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:

    ```
  13. Paraphraser revised this gist Oct 25, 2020. 1 changed file with 12 additions and 11 deletions.
    23 changes: 12 additions & 11 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -13,7 +13,6 @@ This recipe shows you how to get an MQTT payload into an *InfluxDB* database usi

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


    ## Task goal

    Given this MQTT message structure:
    @@ -74,6 +73,8 @@ That alias allows you to connect to the "influx" command line interface (CLI) si
    $ 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:

    ```
    @@ -153,12 +154,12 @@ The expression at step 4 is:
    ```
    [
    {
    "flag": payload.b,
    "discrete": payload.i,
    "continuous": payload.r,
    "message": payload.s
    "flag": msg.payload.b,
    "discrete": msg.payload.i,
    "continuous": msg.payload.r,
    "message": msg.payload.s
    },{
    "identity": payload.t
    "identity": msg.payload.t
    }
    ]
    ```
    @@ -168,10 +169,10 @@ The purpose of this node is to provide a cross-walk between the JSON keys ("b",
    ```
    [
    {
    fieldName : payload.key,
    fieldName : msg.payload.key,
    ...
    },{
    tagName : payload.key,
    tagName : msg.payload.key,
    ...
    }
    ]
    @@ -181,7 +182,7 @@ If you only want to pass fields, then omit the square brackets and the elements

    ```
    {
    fieldName : payload.key,
    fieldName : msg.payload.key,
    ...
    }
    ```
    @@ -261,10 +262,10 @@ Add two "debug" nodes to the canvas. Double-click each in turn and set its Outpu

    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-test-flow.png)

    Click "Deploy" to activate.

    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.

    ```
  14. Paraphraser revised this gist Jul 8, 2020. 1 changed file with 19 additions and 5 deletions.
    24 changes: 19 additions & 5 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -85,7 +85,9 @@ $ influx
    ### 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](https://nodered.org/docs/user-guide/runtime/adding-nodes), select the "Install" tab, then search for and install [node-red-contrib-influxdb](https://flows.nodered.org/node/node-red-contrib-influxdb). If you prefer to install contributions from the command line, do that.
    * Use the main menu (three horizontal bars "≡" at the top, right of the *Node-Red* window) to open the [Palette Manager](https://nodered.org/docs/user-guide/runtime/adding-nodes):
    * 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](https://flows.nodered.org/node/node-red-contrib-influxdb). If you prefer to install contributions from the command line, do that.

    ## A three-node flow

    @@ -105,7 +107,7 @@ Drag an "mqtt in" node onto the canvas. Double-click to open and configure as fo
    * 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 a topic (eg "/site/topic").
    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.
    @@ -171,7 +173,7 @@ The purpose of this node is to provide a cross-walk between the JSON keys ("b",
    },{
    tagName : payload.key,
    ...
    },
    }
    ]
    ```

    @@ -184,9 +186,21 @@ If you only want to pass fields, then omit the square brackets and the elements
    }
    ```

    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. But taking that shortcut carries some risks. For example, 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 the database provides an effective barrier against unexpected keys.
    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, this Javascript expression in the "change" node will result in:
    Given the output from the "mqtt in" node, the Javascript expression in the "change" node will result in:

    ```
    msg.payload = [
  15. Paraphraser revised this gist Mar 2, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -218,7 +218,7 @@ Double-click the "influxdb out" node to open and configure as follows:
    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 the be a static IP address or the fully-qualified domain name of the host running *InfluxDB*.
    * 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".
  16. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 21 additions and 21 deletions.
    42 changes: 21 additions & 21 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -165,47 +165,47 @@ The purpose of this node is to provide a cross-walk between the JSON keys ("b",

    ```
    [
    {
    fieldName : payload.key,
    ...
    },{
    tagName : payload.key,
    ...
    },
    {
    fieldName : payload.key,
    ...
    },{
    tagName : 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 : payload.key,
    ...
    fieldName : 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. But taking that shortcut carries some risks. For example, 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 the database provides an effecive barrier against unexpected keys.
    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. But taking that shortcut carries some risks. For example, 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 the database provides an effective barrier against unexpected keys.

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

    ```
    msg.payload = [
    {
    flag: true,
    discrete: 123,
    continuous: 456.78,
    message: "hello world"
    },{
    identity: "tagValue"
    }
    ]
    {
    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.
    > 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.

    @@ -216,7 +216,7 @@ 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 *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 the 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").
  17. Paraphraser revised this gist Feb 12, 2020. 4 changed files with 0 additions and 0 deletions.
    Binary file removed change-node.png
    View file
    Binary file not shown.
    Binary file removed influxdb-out-node.png
    View file
    Binary file not shown.
    Binary file removed mqtt-in-node.png
    View file
    Binary file not shown.
    Binary file removed test-flow.png
    View file
    Binary file not shown.
  18. Paraphraser revised this gist Feb 12, 2020. No changes.
  19. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -95,7 +95,7 @@ Back in the *Node-Red* main window, click the "+" button to add a new empty flow

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

    ![mqtt-in-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/6c2ecf4e047a65dae0099d58fd754b93c6dd1914/mqtt-in-node.png)
    ![mqtt-in-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-mqtt-in-node.png)

    1. Select the option to add a new mqtt-broker.
    2. Click the pencil icon to open the server properties panel.
    @@ -211,7 +211,7 @@ 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:

    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/6c2ecf4e047a65dae0099d58fd754b93c6dd1914/influxdb-out-node.png)
    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-influxdb-out-node.png)

    1. From the "Server" popup menu, choose "Add new influxdb...".
    2. Click the pencil icon to open the server properties panel.
    @@ -245,7 +245,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/2473fda0897a094ed7b324a7d537ef39ecadf407/test-flow.png)
    ![Node-Red test flow](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-test-flow.png)

    Click "Deploy" to activate.

  20. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/bb04382d8aa5d4529f8a286482e4c3a14990068f/change-node.png)
    ![change-node configuration](https://gist.github.com/Paraphraser/1ea84aad35d1d2f57a23c63caee9302f/raw/790ca37f51d4ace9dff149e360e8d6d0fac6d663/mqtt-node-red-influx-change-node.png)

    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.
  21. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -245,7 +245,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](./test-flow.png)
    ![Node-Red test flow](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/2473fda0897a094ed7b324a7d537ef39ecadf407/test-flow.png)

    Click "Deploy" to activate.

  22. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -95,7 +95,7 @@ Back in the *Node-Red* main window, click the "+" button to add a new empty flow

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

    ![mqtt-in-node configuration](./mqtt-in-node.png)
    ![mqtt-in-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/6c2ecf4e047a65dae0099d58fd754b93c6dd1914/mqtt-in-node.png)

    1. Select the option to add a new mqtt-broker.
    2. Click the pencil icon to open the server properties panel.
    @@ -211,7 +211,7 @@ 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:

    ![influxdb-out-node configuration](./influxdb-out-node.png)
    ![influxdb-out-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/6c2ecf4e047a65dae0099d58fd754b93c6dd1914/influxdb-out-node.png)

    1. From the "Server" popup menu, choose "Add new influxdb...".
    2. Click the pencil icon to open the server properties panel.
  23. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](change-node.png)
    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f/raw/bb04382d8aa5d4529f8a286482e4c3a14990068f/change-node.png)

    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.
  24. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f#file-change-node.png)
    ![change-node configuration](change-node.png)

    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.
  25. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f#file-change-node-png)
    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f#file-change-node.png)

    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.
  26. Paraphraser revised this gist Feb 12, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](./change-node.png)
    ![change-node configuration](https://gist.github.com/Paraphraser/c9db25d131dd4c09848ffb353b69038f#file-change-node-png)

    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.
  27. Paraphraser revised this gist Feb 12, 2020. 5 changed files with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -95,7 +95,7 @@ Back in the *Node-Red* main window, click the "+" button to add a new empty flow

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

    ![mqtt-in-node configuration](./images/mqtt-in-node.png)
    ![mqtt-in-node configuration](./mqtt-in-node.png)

    1. Select the option to add a new mqtt-broker.
    2. Click the pencil icon to open the server properties panel.
    @@ -137,7 +137,7 @@ 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:

    ![change-node configuration](./images/change-node.png)
    ![change-node configuration](./change-node.png)

    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.
    @@ -211,7 +211,7 @@ 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:

    ![influxdb-out-node configuration](./images/influxdb-out-node.png)
    ![influxdb-out-node configuration](./influxdb-out-node.png)

    1. From the "Server" popup menu, choose "Add new influxdb...".
    2. Click the pencil icon to open the server properties panel.
    @@ -245,7 +245,7 @@ Click the Deploy button near the top, right of the canvas.

    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:

    ![Node-Red test flow](./images/test-flow.png)
    ![Node-Red test flow](./test-flow.png)

    Click "Deploy" to activate.

    @@ -340,4 +340,4 @@ When you no longer need the test database, you can remove it like this:
    $ influx
    > DROP TABLE test
    > quit
    ```
    ```
    Binary file added change-node.png
    View file
    Loading
    Sorry, something went wrong. Reload?
    Sorry, we cannot display this file.
    Sorry, this file is invalid so it cannot be displayed.
    Binary file added influxdb-out-node.png
    View file
    Loading
    Sorry, something went wrong. Reload?
    Sorry, we cannot display this file.
    Sorry, this file is invalid so it cannot be displayed.
    Binary file added mqtt-in-node.png
    View file
    Loading
    Sorry, something went wrong. Reload?
    Sorry, we cannot display this file.
    Sorry, this file is invalid so it cannot be displayed.
    Binary file added test-flow.png
    View file
    Loading
    Sorry, something went wrong. Reload?
    Sorry, we cannot display this file.
    Sorry, this file is invalid so it cannot be displayed.
  28. Paraphraser created this gist Feb 12, 2020.
    343 changes: 343 additions & 0 deletions MQTT_Node-Red_Influx.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,343 @@
    # 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](https://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:

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

    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](https://nodered.org/docs/user-guide/runtime/adding-nodes), select the "Install" tab, then search for and install [node-red-contrib-influxdb](https://flows.nodered.org/node/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:

    ![mqtt-in-node configuration](./images/mqtt-in-node.png)

    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 a topic (eg "/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:

    ![change-node configuration](./images/change-node.png)

    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.
    5. Click "Done".
    6. Click "Done" to complete the node setup.

    The expression at step 4 is:

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

    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 : payload.key,
    ...
    },{
    tagName : 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 : 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. But taking that shortcut carries some risks. For example, 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 the database provides an effecive barrier against unexpected keys.

    Given the output from the "mqtt in" node, this 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:

    ![influxdb-out-node configuration](./images/influxdb-out-node.png)

    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 the 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.

    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:

    ![Node-Red test flow](./images/test-flow.png)

    Click "Deploy" to activate.

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

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