What is wrong with Flux query

Fluxlang
,
1

Hello all,
I have started using the InfluxDB 2.x and currently facing problem in understanding the whole flux way of querying the data. In the earlier version in the WHERE clause I could use AND operator and enforce the condition for data what I am looking for for example SELECT "field1",field2,field2" FROM "MEASUREMENT_x WHERE "field1">='condition1' AND "field1" <= 'condition2' AND "field2" >= 'condition3' but unfortunately I am unable to translate the same query with flux. Can someone help me understand the flux query and how I can translate.
Flux query
from(bucket: "some-bucket") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "MEASUREMENT_x") |> filter(fn: (r) => r["_field"] == "field1" and r["_field"] == "field12" and r._value >= 12 and r._value <= 100 and r["_field"] == "field3" and r._value >= 0 and r._value <= 100 ) |> yield()
Thank you in advance!!

2

Hi @Belly_Musketier and kudos for trying to figure out Flux. I believe the key thing here is to use the pivot() function. See my examples below from my dataset that uses two fields (OutdoorHumidity and OutdoorTemp) that were both populated with the same timestamp (this is important because I use it in the pivot function):

Basic query with filters on the field names I have selected. Notice there are two table numbers on the far left: 0 and 1. If we added a third field, it would be labeled as 2.

image
image1855×836 109 KB

Added a pivot function which moves the OutdoorHumidity and OutdoorTemp fields to their own columns:

|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

image
image1855×830 94.3 KB

Added two filters:

  |> filter(fn: (r) => r.OutdoorHumidity > 80)
  |> filter(fn: (r) => r.OutdoorTemp > 54)

image
image1857×860 90.5 KB

Final query would look like this:

from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorHumidity" or r["_field"] == "OutdoorTemp")
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r.OutdoorHumidity > 80)
  |> filter(fn: (r) => r.OutdoorTemp > 54)
  |> yield(name: "your-query")
3

Hello @grant1 Thank you for your response I did think of using Pivot() and Also received the expected results. But later on read about that the Pivot() is performance and memory Intensive?? if yes is there a way to optimize the query with Pivot()

Thanks

4

Y’know, I have read the same things, yet have never experienced performance issues with using the pivot function. I think some queries are scanning such large datasets that it can come into play there, but for many of us, it never becomes an issue.