Filter tags in Flux query based on results in another table

[MzazM]

Hello, I would like to use the measurementid in the table MeasurementInfo below (RepDev0001-Meas001,…), to filter tags in the table Metrics. Then I would like to joini the two resulting tables by measurementid.

MeasurementInfo:

,result,table,measurementid
,_result,0,RepDev0001-Meas001
,_result,0,RepDev0001-Meas002

My (reduced) flux:

import "sql"
MeasurementInfo = sql.from(
driverName: "postgres",
dataSourceName: "postgresql://$user:$password@localhost/mymetadata?sslmode=disable",
query: "SELECT MeasurementID FROM Measurement WHERE ..."
)

Metrics = from(bucket: "${bucket}")
  |> range($range)
  |> filter(fn: (r) => r._measurement == "Phasor") 
  // how to get the RepDev to automatically be present below (and not hardcoded)?
  |> filter(fn: (r) => r["measurementid"] =~ /RepDev0001-Meas001|RepDev0001-Meas002.../) 

data = join(tables: {metric: Metrics, info: MeasurementInfo}, on: ["measurementid"])
...

I know I could join immediately MeasurementInfo and Metrics right after having filtered with r. _measurement==“Phasor”, and this would automatically do the filtering, but “join” is a non-pushdown function. Therefore, I think it is way lighter to call join after having reduced the Metrics table as much as possible. I will have thousands of measurementsid in the Metrics table and only few to display (those in the MeasurementInfo).

My feeling is that I should use tableFind() and getColumn() to somehow extract the list of measurementIDs from MeasurementInfo and somehow insert them in the Metrics query, but everything I tried failed. I feel there should be a simpler way.
Any hint?

[scott]

@MzazM You are definitely on the right path. Try this:

import "sql"

measurementIDs = sql.from(
    driverName: "postgres",
    dataSourceName: "postgresql://$user:$password@localhost/mymetadata?sslmode=disable",
    query: "SELECT MeasurementID FROM Measurement WHERE ..."
  )
  |> tableFind(fn: (key) => true)
  |> getColumn(column: "measurementid")

from(bucket: "${bucket}")
  |> range($range)
  |> filter(fn: (r) => r._measurement == "Phasor") 
  |> filter(fn: (r) => contains(value: r.measurementid, set: measurementIDs)

measurementIDs returns an array of values extracted from the measurementid column in your SQL data. You can then use contains() to check if a row’s measurementid is in the measurementIDs list. It returns true if the value does exists in the set/list and false if it doesn’t.