Flux: Simple way to query multiple measurements & return ONE table

Fluxlang
1

Hello,

I am collecting data for my smart home from a lot of sensors scattered around the house. Recently an upgrade of one of the data collectors caused measurements to be renamed so I now have multiple measurements to query for many of my dashboards. Typical queries now look like this:

from(bucket: "iobroker/global")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    ( r._measurement = "old-measurement" or
      r._measurement == "new-measurement" ) and
    r._field == "value"
  )

This of course returns two sets of results because the measurements are separate.
But since the timestamps of the measurements don’t overlap I would like to treat these results as ONE table, for plotting (just one legend, one color, etc) and for further processing.

How do I use join() or similar in a generic & simple way, preferably without having to explicitly name all measurements (I already filtered them!), so that only one table is returned by this query?

Thank you :slight_smile:

2

Hi, get rid of the _measurement column and sort by time.
You will end up with a single sorted table:

from(bucket: "iobroker/global")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    ( r._measurement = "old-measurement" or
      r._measurement == "new-measurement" ) and
    r._field == "value"
  )
 |> drop(columns: ["_measurement"])
 |> sort(columns: ["_time"], desc: false)

Kind regards,
Domenico

1 Like
3

Please note that if you have just “old-measurement” and “new-measurement” in your bucket, you can simply filter for _field and this will work even if the _measurement name will change again in the future:

from(bucket: "iobroker/global")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._field == "value")
  |> drop(columns: ["_measurement"])
  |> sort(columns: ["_time"], desc: false)
1 Like