Grab values within a record

InfluxDB 2
1

I use a smartMeater (bitShake) to extract data from my meter box.

The data is forwarded to brokerio via MQTT and stored in influxdb.

RAW DATA in influxdb:

{“Time”:“2024-08-16T11:19:04”,“eHZ”:{“E_in”:18078.365,“E_out”:0.000,“Power”:-397,“Power2”:0}}

Query

from(bucket: “iobroker”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “stromzaehler.verbrauch”)
|> filter(fn: (r) => r[“_field”] == “value”)

I can’t figure out how to grab the values inside the curly brackets (JSON), like “Power” or “E_in”.

2

Welcome @tyel to the InfluxDB forum.

You are storing records like this every second (minute, hour, etc.), correct? So your data in Influx has both a time column and a string like this?

{“Time”:“2024-08-16T11:19:04”,“eHZ”:{“E_in”:18078.365,“E_out”:0.000,“Power”:-397,“Power2”:0} }

Is the “Time” field in the above string identical to the time value you have stored in the time column of your Influx database?

A few ideas come to mind, but there are 2 general approaches:

ETL = extract, transform, load where you extract the JSON string and transform it using Telegraf or Node-RED or Python or whatever, and then load the cleaned up data into InfluxDB with the time and data that you want.

ELT = extract, load, transform where you extract the JSON string and load it into InfluxDB, then try to figure out how to transform it using InfluxDB or Grafana or Regex or whatever else you might have available. If you go this route (as you seem to have done thus far), this might help:

But my personal opinion is to do all the transforming BEFORE you load it into InfluxDB.

2 Likes
3

Thanks for your perspective and suggestions.
I was able to transfer the transformed values to influxDB with Node-Red using the “change” Element and the correct topic “payload.sn.eHZ.Power”.

1 Like