Where clauses in Flux query

Fluxlang
1

Hi guys,
Am trying to add where clause in my flux query but it’s not working. I have tried the following methods but no result

  1. from(bucket: “L_OEEA_COMPACT”)
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == “RT_Dashboard”)
    |> filter(fn: (r) => r["_field"] == “Availability”)
    |> filter(fn: (r) => r[“StationID”] == 2)
    |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
    |> yield(name: “last”)

  2. from(bucket: “L_OEEA_COMPACT”)
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == “RT_Dashboard”)
    |> filter(fn: (r) => r["_field"] == “Availability” and r[“StationID”] == 2)
    |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
    |> yield(name: “last”)

2

I think you have to do something like this:

from(bucket: "L_OEEA_COMPACT")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "RT_Dashboard" and r._field =~ /Availability|StationID/)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> filter(fn: (r) => r.StationID == 2)
    |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
    |> yield(name: "last")

I’m not sure about the second filter, so try running without it just to show that you get some data.

3

Nope, didn’t work… Gives error _value column doesn’t exists. If I try to replace it with the required field name, then also same error

4

Hello @asthomas,
What would your where clause look like in SQL or InfluxQL? I’m having trouble understanding what you’re trying to do.

5

Hello @Anaisdg. I don’t know either. I was taking a reasonable guess from @Aritra666B’s question. It looks like the goal is to create records that contain both StationID and Availability as columns, for any record where StationID == 2. I am guessing that StationID and Availability are fields, but I don’t know the data schema.

1 Like
6

Hello @Anaisdg,

My target is to get values of columns like Availability based on the StationID value 1 or 2.
In older version(1.8) I had no trouble doing this, but since we are now unable to do it using Flux query.

My SQL query looked like this:
SELECT $__time(EntryTimeStamp),
Availability as ‘Availability’
FROM dbo.RT_DashBoard WHERE StationID=$StationID

7

So I was trying something for the where clause:
from(bucket: “L_OEEA_COMPACT”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “RT_ProductionLog”)
|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Mode”)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> map(fn: (r) => ({ r with _value: r.Mode }))
|> filter(fn: (r) => r.StationID == ${StationID})
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> yield(name: “last”)

Now this works fine for integer fields, but for string type it returns 2 twice the values.

8

Hello @Aritra666B.
What do you mean by

2 twice the values

You’re saying the string values are getting multiplied by 2?
That seems implausible since you’re not multiplying by 2 anywhere.
You can convert all the values to int with the toInt() function before the pivot function.

You might also want to be aware of:

9

image

from(bucket: “L_OEEA_COMPACT”)

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)

|> filter(fn: (r) => r["_measurement"] == “RT_ProductionLog”)

|> filter(fn: (r) => r["_field"] == “StationID” or r["_field"] == “Mode”)

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

|> map(fn: (r) => ({ r with _value: r.Mode }))

|> filter(fn: (r) => r.StationID == 2)

|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

|> yield(name: “last”)