Using results of one query as a filter for another

[Brian_Gibson]

Hi,

So here is what I am trying to do…

Take this query…

dbs = from(bucket: “data”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “oracle_ash”)
|> group(columns: [“database”])
|> aggregateWindow(every: 5m, fn: count)
|> aggregateWindow(every: -v.timeRangeStart, fn: sum)
|> group()
|> top(n:5)
|> keep(columns:[“database”])

This returns a list of 5 values from the column database. I want to take those 5 values and use them to filter a different query. But I can’t figure out the nomenclature for using a variable in the filter function, much less how to get all values included.

[Ravikant_Gautam]

I am to save the top 5 filtered queries into the different bucket or same bucket and in different measurements and from there you can use those 5 results for anything.

[Anaisdg]

Hello @Brian_Gibson,
You can use the findColumn() function to extract an array from your table stream dbs
https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/stream-table/findcolumn/

It would look something like:

dbs = from(bucket: “data”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “oracle_ash”)
|> group(columns: [“database”])
|> aggregateWindow(every: 5m, fn: count)
|> aggregateWindow(every: -v.timeRangeStart, fn: sum)
|> group()
|> top(n:5)
|> keep(columns:[“database”])
|> findColumn(
      fn: (key) => key._field == "fieldName",
      column: "_value"
    )

from(bucket: “otherData”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == dbs[0])

Keep in mind you might need to convert the values to a string first if they’re not already.
https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/type-conversions/tostring/

[Brian_Gibson]

Thank you for the response. Unfortunately I can’t get the findcolumn function to work. I think the reason is because I don’t have a group key. I’ve tried to use the database column but obviously that isn’t a group key. I tried the measurement value but that also didn’t work.

Ultimately what I’m trying to do is graph only the top 5 values by database. Getting a point in time top 5 list is easy enough. But getting a graph set is difficult because I need to come up with a value per database and then filter the top 5 databases.