Telegraf: Querying json data with different levels from influx 2

InfluxDB 2
1

Hi,

I’m trying to get data from mqtt into Influxdb 2.0 and from there into Grafana. The data I have in MQTT is in this format:

{
  "id": 452664778,
  "channel": 0,
  "from": 2130636288,
  "payload": {
    "hardware": 10,
    "id": "!7efeee00",
    "longname": "base0",
    "shortname": "BA0"
  },
  "sender": "!7efeee00",
  "timestamp": 1646832724,
  "to": -1,
  "type": "nodeinfo"
}

I have telegraf configured like this, since the above json is stored in the /msh topic:

[[inputs.mqtt_consumer]]
servers = ["tcp://192.168.1.10:1883"]
topics = [
     "msh/#",
]
data_format = "json"

In my Influx query I now want to get all payload_voltage readings where from equals a certain value. I’m stuck at achieving that. I started with this query:

from(bucket: "iot")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["_field"] == "from" or r["_field"] == "payload_voltage")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Then I tried to filter it like so:

from(bucket: "iot")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["_field"] == "from" or r["_field"] == "payload_voltage")
  |> filter(fn: (r) => r["_value"] == 3394052642)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

3394052642 is the _from value. So as I said, I want to get all payload_voltage values where _from = 3394052642.That however just leaves me with all the _from values which isn’t what I want.

Is this problem something I can achieve with my existing setup? I think the problem arises from the json structure: I can’t seem to connect all the different json-indentations in my database.

Looking forward to any little piece of advice!
Thanks!
pk