dersimn/mqtt-json2influxdb repository overview

Dumps MQTT messages to InfluxDB using InfluxQL (for InfluxDB < 2.0).
It tries to parse JSON formatted messages with a fallback for raw strings (e.g. unquoted strings sent over MQTT).

⁠Supported Tags

• The tags are based on the SemVer convention, e.g.: 2, 2.1, 2.1.6 point to the same image (at least at one point in time).
• Tags with simple versions are updated every time the base image is updated, so for example 2.1.6 will be a different SHA when the underlying base image is updated on Docker Hub.
• If you want to stay on the same image and not receive any updates, use a timestamped tag, e.g.: 2.1.6-build20230915T0815.

⁠Usage

docker run -d \
    --restart=always \
    --name=mqtt-json2influxdb \
    --add-host=host.docker.internal:host-gateway \
    dersimn/mqtt-json2influxdb \
        --mqtt-url mqtt://host.docker.internal \
        --influxdb-url http://username:[email protected]:8086/databasename

Run docker run --rm dersimn/mqtt-json2influxdb -h for a list of options.

If you prefer configuring the script by ENVs the upper command can also be written as:

docker run -d \
    --restart=always \
    --name=mqtt-json2influxdb \
    --add-host=host.docker.internal:host-gateway \
    -e MQTTJSON2INFLUXDB_MQTT_URL=mqtt://host.docker.internal \
    -e MQTTJSON2INFLUXDB_INFLUXDB_URL=http://username:[email protected]:8086/databasename \
    dersimn/mqtt-json2influxdb

⁠InfluxDB Type Conversions

MQTT allows sending all types of binary data, but most users use it to send UTF-8 encoded strings or JSON strings.
The InfluxDB line protocol (used by InfluxDB v1) allows storing the basic data types: floats, integers, strings, or Booleans⁠ - but not objects or arrays.

A big problem with InfluxDB is that you can't switch data types. A field key once filled with an integer cannot be used to store a string afterwards. Therefore the data type is appended behind each field key and sometimes it is also tried to convert the data types. What I've tried to do here is to find a format that works in most cases and that allows you to store everything in an MQTT message without having to think too much.

For example, if you send the string 42 (without quotes) to topic test/topic, it can be interpreted as a JSON formatted number, so it will be written to InfluxDB: measurement=test/topic, field-key1=payload__integer, field-value1=42 (as float). Because JSON does not distinguish between float and integer, JSON-numbers are always stored as InfluxDB-float.

If a larger JSON object is sent via MQTT, multiple field-key/value pairs are written per InfluxDB measurement, for example: {"foo":42, "bar": "baz"} → measurement=test/topic, field-key1=payload.foo__integer, field-value1=42, field-key2=payload.bar__string, field-value2="baz".

⁠Example conversions

null → payload__type = "null"

<zero bytes payload> → payload__type = "empty"

true → payload__type = "boolean"
       payload__boolean = true
       payload__number = 1

42 → payload__type = "number"
     payload__number = 42

"42" → payload__type = "string"
       payload__string: "42"
       payload__number: 42

Note: A quoted string "42" will be interpreted as JSON-string, a 42 (without quotes) will be interpreted as JSON-integer. For un-quoted strings that can't be converted to a JSON data type, we use raw-string in payload__type:

foo → payload__type = "raw-string"
      payload__string = "foo"

"foo" → payload__type = "string"
        payload__string = "foo"

[42, "foo", 3.14, false] → payload__type = "array"
                           payload.0__type = "number"
                           payload.0__number = 42
                           payload.1__type = "string"
                           payload.1__string = "foo"
                           payload.2__type = "number"
                           payload.2__number = 3.14
                           payload.3__type = "boolean"
                           payload.3__boolean = false
                           payload.3__number = 0

{"foo": "bar"} → payload__type = "object"
                 payload.foo__type = "string"
                 payload.foo__string = "bar"

{"foo": {"bar": "baz"}} → payload__type = "object"
                          payload.foo.bar__type = "string"
                          payload.foo.bar__string = "baz"

[1,[2,3,],{"foo": "bar"}] → payload__type = "array"
                            payload.0__type = "number"
                            payload.0__number = 1
                            payload.1.0__type = "number"
                            payload.1.0__number = 2
                            payload.1.1__type = "number"
                            payload.1.1__number = 3
                            payload.2.foo__type = "string"
                            payload.2.foo__string = "bar"

⁠Additional Type Conversions

• boolean → number (0, 1), because Grafana can't display boolean values in a graph using InfluxQL (it only works using Flux with InfluxDB 2.x).
• We also try to convert string → number.
• Special strings like yes/no, on/off,… will be converted to boolean values.

⁠Source

GitHub: dersimn/mqtt-json2influxdb⁠