Search code examples
influxdb-2

Influxdb with pivot and map function

I have a Shelly Em3 writing the power values of 3 phases into a influxdb.

Values are there, I can read them successfully. I also got the pivot and map function running to do the calculation of the total for me. I am not able show the four values in a nice graph. Would please some one support me here?

This simple query works super:

from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "meter")
  |> filter(fn: (r) => r["_field"] == "1_power" or r["_field"] == "2_power" or r["_field"] == "0_power")
  |> yield(name: "mean")

Adding a pivot function also works like I expect it to work:

from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "meter")
  |> filter(fn: (r) => r["_field"] == "1_power" or r["_field"] == "2_power" or r["_field"] == "0_power")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> yield(name: "mean")

enter image description here

Now adding the map function results into a nice table with all four columns:

from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "meter")
  |> filter(fn: (r) => r["_field"] == "1_power" or r["_field"] == "2_power" or r["_field"] == "0_power")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with total_power: r["0_power"] + r["1_power"] + r["2_power"] }))

enter image description here

But how the hack can I now show these four values in a graph?

I am sure that it is just a single foot step away, but I don't get it running.

enter image description here

DB Version is 2.61

Solution

  • You can yield multiple results from your query, one for each power and second for the total value.

    data = from(bucket: "mybucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "meter")
  |> filter(fn: (r) => r["_field"] == "1_power" or r["_field"] == "2_power") or r["_field"] == "0_power")

data
  |> yield("powers")

data
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r["0_power"] + r["1_power"] + r["2_power"] }))
  |> yield("total")