InfluxDB - Sub query or Nested query alternative for flux

I have measurement/table like this in Influx database

time machine_id _field _value
time1 1 status true
time2 2 status false
time3 1 status false
time2 2 status true

Then I created a new table using the above data and Flux query:
(below table is grouped by id)

machine_id last_false_time
1 time3
2 time2

Now I have to find number of ‘true’ entries per machine since its last_false_time and store the result into a table.

Is there any way to achieve this outcome using Flux query?

Hi, could you please give any hint on this, @Anaisdg @scott

hello @Rahul_Sangwan,
Do you only have 2 machine_id? Or more?
If you have only 2 machines (or limited number) you could:

  1. extract the time3 and time2 with findRecord()
    findRecord() function | Flux 0.x Documentation
  2. Then you could filter conditionally for each id and where time is greater than respective times and then count
    Query using conditional logic in Flux | InfluxDB OSS 2.6 Documentation

Or if you have N machine ids and you don’t want to do that systematic approach you could instead:

  1. join data on machine_id
    Join data in InfluxDB with Flux | InfluxDB OSS 2.6 Documentation
  2. then fill previous on the last_false_time
    Fill null values in data | InfluxDB OSS 2.6 Documentation
  3. conditional filter where time > last_false_time
    same docs as above

Let me know if you want example queries or if the documentation provided is enough to get you started.
Thank you!