1

I'm quite new to influx and the influx query language. I'm trying to query data from Grafana. In my InflluxDB the measurement data fields can contain three measurements. But: not everytime a measurement is taken, all three possible values are measured and therefore not stored with the same timestamp. Now I want to filter out the rows where only all three values exist in the data. I do not want to combine data with timestamps in a certain range, i specifically only want the data where all three values are present.

My current query looks like this:

from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "my_data")
  |> filter(fn: (r) => r["_field"] == "temp1"  or r["_field"] == "temp2" or r["_field"] == "temp3" )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> yield()

As an output i get all the lines where either temp1, temp2, or temp3 are present. But I want only the rows, where all three are present.

I'm pretty sure I'm missing some very easy solution here, but was not able to find anything suitable online. It seems that teh function contains() does basically the same as my filter line.

jonas37
  • 63
  • 7

1 Answers1

2

You could try use the exists operator.

That is:

from(bucket: "my_bucket")
     |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
     |> filter(fn: (r) => r["_measurement"] == "my_data")
     |> filter(fn: (r) => r["_field"] == "temp1"  or r["_field"] == "temp2" or r["_field"] == "temp3" )
     |> filter(fn: (r) => exists r._value)  // this line will filter out null values
     |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
     |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
     |> yield()
Munin
  • 1,576
  • 2
  • 19
  • Thanks for your answer, did not quite what I wanted. But the exists operator was the right hint. After the pivot I added `|> filter(fn: (r) => (exists r["temp1"] and exists r["temp2"] and exists r["temp3"] ))`. Now it works like a charm. Thanks – jonas37 Nov 04 '22 at 09:14
  • Glad it helps in some way. – Munin Nov 04 '22 at 10:40