CODEWITHSUNDEEP
grafanainfluxdb
jonas37
jonas37

Reputation: 73

InfluxDB Query How to filter out data where row does not exist

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.

Upvotes: 2

Views: 1895

Answers (1)

Munin
Munin

Reputation: 1639

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()

Upvotes: 2

Related Questions