Is there a better way to write my request?

InfluxDB 2
1

Hi

I am looking for the list of “parameters” recorded as “friendly names”.
I am not looking for any value just the list of sensors type based on their “friendly name” tag starting by "temp "

in SQL I would do a

SELECT distinct  friendly_name FROM database WHERE friendly_name like 'temp %'

but in flux I had to do this :

from(bucket: "home_assistant")
  |> range(start: -1d)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["friendly_name"] =~ /temp .*/)
  |> keep(columns: ["friendly_name"])
  |> distinct()
  |> group()
  |> yield()

it is working but seems quite complex, is there a better way to do it ?

2

Its because you are used to SQL, i find Flux easier to read than SQL but I did not knew SQL before.

but in your example if your data schema does not have any other measurement with the tag “fiendly_name” like temp, you could omit

 |> filter(fn: (r) => r["_measurement"] == "°C")

in my experience is fast if you are more specific first, flux has a cascading effect, use the simplest filter that narrows down the most first.

1 Like
3

Rght now I am struggling to write some SQL queries and this is when I appreciate the structure of FLUX scripting. on SQL the Query needs to be nested bottom to top with multiple SELECT statements and then at the end do Joins, it kind of difficult to track in my mind.

Flux by the other hand is top to bottom I can grasp the result as I am narrowing down, using group and declaring variables. Usually, I have all the subsets already in the schema, so no need to use multiple select statements with aggregators like SELECT (SELECT MAX( CASE WHEN a=‘b’ FROM c AS d))… for each one of the parameters I am looking for.

and at the very end I can use last() or min() to get the latest or first values.

I mean time series and relational databases are not the same and were never intended to be, but it requires a different creative approach …