extract values from TTN payload to feed into Influxdb

[Peter Segner]

Hi guys,

I want to change the output from an MQTT broker (TTN network) into a message I can store into an influxdb. The output I have got from the MQTT broker is the following:

object

app_id: "bme280sensortest"

dev_id: "loranode1"

hardware_serial: "0026BD7D7CD0A6CB"

port: 1

counter: 451

payload_raw: "AWcAtQJzJlkDaIwEAg1I"

payload_fields: object

analog_in_4: 34

barometric_pressure_2: 981.7

relative_humidity_3: 70

temperature_1: 18.1

I want to store a timestamp, analog_in_4 (is the battery value of the node), barometric_pressure_2, relative_humidity_3 and temperature_1 into an influxdb (or optional mysql db).
I am able to receive the selected values from the MQTT broker, when I subscribe to them individually (e.g.: bme280sensortest/devices/loranode1/up/temperature_1).
But I would be happy if I could get them by splitting the payload appropriately and store them then into Influx.

Your feedback is very much appreciated !

Peter

[Colin Law]

Not exactly sure what you are asking. You say that you can receive them individually via mqtt, but in addition you show an object that shows them all.

Colin

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/faa793d2-1241-47b8-ba0e-7a2a86be6082%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Peter Segner]

First of all I want to receive the object from the MQTT broker, splitt them accordingly into fields and field values, show them in a dashboard and store then the field:value pairs with an timestamp into influx.

But I was not successfull so I tried to get the values from the broker by subscribe them individiually.

Now I have the JSON object:

27.12.2017, 17:44:50node: JSON

bme280sensortest/devices/loranode1/up : msg.payload : Object

object

app_id: "bme280sensortest"

dev_id: "loranode1"

hardware_serial: "0026BD7D7CD0A6CB"

port: 1

counter: 451

payload_raw: "AWcAtQJzJlkDaIwEAg1I"

payload_fields: object

analog_in_4: 34

barometric_pressure_2: 981.7

relative_humidity_3: 70

temperature_1: 18.1

and here is my flow:

[
    {
        "id": "1805cb4e.d51d25",
        "type": "mqtt in",
        "z": "2b8c1d8a.6c28ba",
        "name": "MQTT Node - TheThingsNetwork",
        "topic": "#",
        "qos": "2",
        "broker": "19986064.b4bb7",
        "x": 125,
        "y": 110.5,
        "wires": [
            [
                "cdb9ba77.4d97e8"
            ]
        ],
        "outputLabels": [
            "All"
        ]
    },
    {
        "id": "a9e12de2.bd917",
        "type": "influxdb out",
        "z": "2b8c1d8a.6c28ba",
        "influxdb": "6f9a1db.39f36e4",
        "name": "",
        "measurement": "",
        "precision": "",
        "retentionPolicy": "",
        "x": 820,
        "y": 540,
        "wires": []
    },
    {
        "id": "cdb9ba77.4d97e8",
        "type": "json",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "pretty": false,
        "x": 410,
        "y": 40,
        "wires": [
            [
                "d3f55ed7.091a38",
                "8ba95514.f37f98"
            ]
        ]
    },
    {
        "id": "d3f55ed7.091a38",
        "type": "debug",
        "z": "2b8c1d8a.6c28ba",
        "name": "JSON",
        "active": true,
        "console": "false",
        "complete": "payload",
        "x": 710,
        "y": 40,
        "wires": []
    },
    {
        "id": "b475105c.6ae458",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Temp",
        "label": "Celsius",
        "format": " {{value}}",
        "min": "10",
        "max": "30",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 350,
        "y": 300,
        "wires": []
    },
    {
        "id": "d66e52e8.b6bb38",
        "type": "mqtt in",
        "z": "2b8c1d8a.6c28ba",
        "name": "Temperature",
        "topic": "bme280sensortest/devices/loranode1/up/temperature_1",
        "qos": "2",
        "broker": "19986064.b4bb7",
        "x": 90,
        "y": 300,
        "wires": [
            [
                "b475105c.6ae458",
                "bd0f9007.7f5ac"
            ]
        ]
    },
    {
        "id": "3008f035.eed17",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Barometric Pressure",
        "label": "hPa",
        "format": "{{value}}",
        "min": "1000",
        "max": "1100",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 400,
        "y": 360,
        "wires": []
    },
    {
        "id": "fe64850d.5cdcb8",
        "type": "mqtt in",
        "z": "2b8c1d8a.6c28ba",
        "name": "Barometric Pressure",
        "topic": "bme280sensortest/devices/loranode1/up/barometric_pressure_2",
        "qos": "2",
        "broker": "19986064.b4bb7",
        "x": 110,
        "y": 360,
        "wires": [
            [
                "3008f035.eed17",
                "9430440e.143fc"
            ]
        ]
    },
    {
        "id": "b09d7d9c.86276",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Humidity",
        "label": "Prozent",
        "format": "{{value}}",
        "min": 0,
        "max": "100",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 360,
        "y": 420,
        "wires": []
    },
    {
        "id": "2a7e8e5c.4a4cba",
        "type": "mqtt in",
        "z": "2b8c1d8a.6c28ba",
        "name": "Humidity",
        "topic": "bme280sensortest/devices/loranode1/up/relative_humidity_3",
        "qos": "2",
        "broker": "19986064.b4bb7",
        "x": 80,
        "y": 420,
        "wires": [
            [
                "b09d7d9c.86276",
                "eb5f13b6.2b5168"
            ]
        ]
    },
    {
        "id": "e08b401.a2da74",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Battery Level Sensor Node",
        "label": "Volt",
        "format": "{{value}}",
        "min": "30",
        "max": "40",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 420,
        "y": 480,
        "wires": []
    },
    {
        "id": "5f7abdf4.49e45c",
        "type": "mqtt in",
        "z": "2b8c1d8a.6c28ba",
        "name": "Battery",
        "topic": "bme280sensortest/devices/loranode1/up/analog_in_4",
        "qos": "2",
        "broker": "19986064.b4bb7",
        "x": 70,
        "y": 480,
        "wires": [
            [
                "e08b401.a2da74",
                "e8302b1d.abcf28"
            ]
        ]
    },
    {
        "id": "bd0f9007.7f5ac",
        "type": "delay",
        "z": "2b8c1d8a.6c28ba",
        "name": "Random delay",
        "pauseType": "random",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "3",
        "randomUnits": "seconds",
        "drop": false,
        "x": 380,
        "y": 560,
        "wires": [
            [
                "1d87c8da.2d9117"
            ]
        ]
    },
    {
        "id": "1d87c8da.2d9117",
        "type": "function",
        "z": "2b8c1d8a.6c28ba",
        "name": "Wait for all tasks to finish",
        "func": "context.data = context.data || new Object();\n\nswitch (msg.topic) {\n    case \"temperature_1\":\n        context.data.task1 = msg.payload;\n        msg = null;\n        break;\n    case \"barometric_pressure_2\":\n        context.data.task2 = msg.payload;\n        msg = null;\n        break;\n    case \"relative_humidity_3\":\n        context.data.task3 = msg.payload;\n        msg = null;\n        break;  \n    case \"analog_in_4\":\n        context.data.task4 = msg.payload;\n        msg = null;\n        break;\n        \n    default:\n        msg = null;\n    \tbreak;\n\n}\n\nif(context.data.task1 != null && context.data.task2 != null && context.data.task3 != null \n&& context.data.task4 != nul4 ) {\n\tmsg2 = new Object();\n    msg2 = context.data;\n    context.data=null;\n\treturn msg2;\n} else return msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 610,
        "y": 620,
        "wires": [
            [
                "a9e12de2.bd917",
                "877f7489.4acbe8"
            ]
        ]
    },
    {
        "id": "9430440e.143fc",
        "type": "delay",
        "z": "2b8c1d8a.6c28ba",
        "name": "Random delay",
        "pauseType": "random",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "3",
        "randomUnits": "seconds",
        "drop": false,
        "x": 380,
        "y": 600,
        "wires": [
            [
                "1d87c8da.2d9117"
            ]
        ]
    },
    {
        "id": "eb5f13b6.2b5168",
        "type": "delay",
        "z": "2b8c1d8a.6c28ba",
        "name": "Random delay",
        "pauseType": "random",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "3",
        "randomUnits": "seconds",
        "drop": false,
        "x": 380,
        "y": 640,
        "wires": [
            [
                "1d87c8da.2d9117"
            ]
        ]
    },
    {
        "id": "e8302b1d.abcf28",
        "type": "delay",
        "z": "2b8c1d8a.6c28ba",
        "name": "Random delay",
        "pauseType": "random",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "3",
        "randomUnits": "seconds",
        "drop": false,
        "x": 380,
        "y": 680,
        "wires": [
            [
                "1d87c8da.2d9117"
            ]
        ]
    },
    {
        "id": "877f7489.4acbe8",
        "type": "debug",
        "z": "2b8c1d8a.6c28ba",
        "name": "JoinNodeTTN",
        "active": true,
        "console": "false",
        "complete": "payload",
        "x": 820,
        "y": 700,
        "wires": []
    },
    {
        "id": "8ba95514.f37f98",
        "type": "split",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "splt": ",",
        "spltType": "str",
        "arraySplt": "1",
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 410,
        "y": 120,
        "wires": [
            [
                "41241ae9.1233a4",
                "d280b6ed.2aa728"
            ]
        ]
    },
    {
        "id": "41241ae9.1233a4",
        "type": "switch",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "cont",
                "v": "\"analog_in_4\":",
                "vt": "str"
            },
            {
                "t": "cont",
                "v": "\"barometric_pressure_2\":",
                "vt": "str"
            },
            {
                "t": "cont",
                "v": "\"relative_humidity_3\":",
                "vt": "str"
            },
            {
                "t": "cont",
                "v": "\"temperature_1\":",
                "vt": "str"
            }
        ],
        "checkall": "true",
        "outputs": 4,
        "x": 550,
        "y": 180,
        "wires": [
            [
                "7e200176.f51448"
            ],
            [
                "ffd4c048.b102"
            ],
            [
                "7d1065f3.7272e4"
            ],
            [
                "b40ae61c.9809f8"
            ]
        ]
    },
    {
        "id": "7e200176.f51448",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Analog",
        "label": "units",
        "format": "{{value}}",
        "min": 0,
        "max": 10,
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 760,
        "y": 160,
        "wires": []
    },
    {
        "id": "7d1065f3.7272e4",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Humidity",
        "label": "units",
        "format": "{{value}}",
        "min": 0,
        "max": 10,
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 760,
        "y": 240,
        "wires": []
    },
    {
        "id": "b40ae61c.9809f8",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Temperature",
        "label": "units",
        "format": "{{value}}",
        "min": 0,
        "max": 10,
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 770,
        "y": 280,
        "wires": []
    },
    {
        "id": "d280b6ed.2aa728",
        "type": "debug",
        "z": "2b8c1d8a.6c28ba",
        "name": "Split",
        "active": true,
        "console": "false",
        "complete": "payload",
        "x": 670,
        "y": 100,
        "wires": []
    },
    {
        "id": "ffd4c048.b102",
        "type": "ui_gauge",
        "z": "2b8c1d8a.6c28ba",
        "name": "",
        "group": "3c8dbb39.f8de0c",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Pressure",
        "label": "units",
        "format": "{{value}}",
        "min": 0,
        "max": 10,
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "x": 760,
        "y": 200,
        "wires": []
    },
    {
        "id": "19986064.b4bb7",
        "type": "mqtt-broker",
        "z": "2b8c1d8a.6c28ba",
        "broker": "eu.thethings.network",
        "port": "1883",
        "clientid": "",
        "usetls": false,
        "verifyservercert": true,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "willTopic": "",
        "willQos": "0",
        "willRetain": null,
        "willPayload": "",
        "birthTopic": "",
        "birthQos": "0",
        "birthRetain": null,
        "birthPayload": ""
    },
    {
        "id": "6f9a1db.39f36e4",
        "type": "influxdb",
        "z": "",
        "hostname": "192.168.178.126",
        "port": "8086",
        "protocol": "http",
        "database": "ttndb",
        "name": "",
        "usetls": false,
        "tls": ""
    },
    {
        "id": "3c8dbb39.f8de0c",
        "type": "ui_group",
        "z": "",
        "name": "Sensor Düsseldorf",
        "tab": "234eafb3.3f6c7",
        "order": 1,
        "disp": true,
        "width": "6"
    },
    {
        "id": "234eafb3.3f6c7",
        "type": "ui_tab",
        "z": "",
        "name": "Motes",
        "icon": "dashboard",
        "order": 1
    }
]
 
      

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

[Colin Law]

So which bit do you need help with? Do things one at a time, then you will move towards the final solution.

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/6d09b765-fb43-4daf-a2c7-6d4d19a6925b%40googlegroups.com.

[Peter Segner]

I have no glue how to access the different key:value pairs from the JSON object. E.g. to access the barometric_pressure_2 with the value of : 981.7
Then I can do that for all and later on construct a proper message for the influxdb insert.
Thanks Colin

[Colin Law]

If you are in a function node then just use msg.payload.id_name (for example), similarly you can access them in a change node.  You have almost the right format for influx insert already, you just need to strip out the properties you don't want (such as app_id) then pass it direct to the influx node.

So in a function node you could do

delete msg.payload.id_name

delete msg.payload.dev_id

and so on. At least I think that is right.

Then pass the message on to the influx node.

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/842ff979-eb4c-4145-8ae7-b461d68f5e97%40googlegroups.com.

[Peter Segner]

Sounds good. I will try.
I missed the feature that I can access them via: msg.payload.id_name.
Thanks in advance Colin

[Colin Law]

On 27 December 2017 at 17:42, Garry Hayne <garry...@gmail.com> wrote:

Hi,

you will have to store them into individual measurements in influxdb anyway, I would take them individually, influxdb will create timestamps automatically for each measurement.

The influx node will take a payload of key/value pairs and store them all against the same timestamp. No need to split it up into individual channels.

Colin

 

Garry

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/7431205c-33f8-409d-8f1d-eea4b3a7020c%40googlegroups.com.

[Garry Hayne]

I had already deleted the post after I realised my error Colin, you were quicker :)

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

[Colin Law]

You can't delete a post on a mailing list.

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/9f58f3cf-9ae9-4634-b75d-ba5db8040187%40googlegroups.com.