Limit() with multiple optional fields (limit limits each field separately)

Hello,

I have a multivariate time series with a flexible field set:

  • 1991-01-01T00:00:00Z: a=1
  • 1992-01-01T00:00:00Z: a=2, b=2.5
  • 1993-01-01T00:00:00Z: a=3, b=3.5

As you can see, variable b only gets measured from 1992 onwards.

Now, when I want to paginate through all my records with a limit of 2 with Flux, I do:

from(bucket: "mybucket")
    |> range(start: 1970-01-01T00:00:00Z)
    |> filter(fn: (r) => r._measurement == "mymeasurement")
    |> limit(n: 2)
    |> yield()

Expected:
I would expect to get the records for 1991 and 1992.

  • 1991-01-01T00:00:00Z: a=1
  • 1992-01-01T00:00:00Z: a=2, b=2.5

Actual:
However, it seems that Influx limits each field separately. Because these are the FluxTables’ records I get:

{result=_result, table=0, _start=1970-01-01T00:00:00Z, _stop=2020-09-10T09:40:34.496818215Z, _time=1991-01-01T00:00:00Z, _value=1, _field=a, _measurement=mymeasurement}
{result=_result, table=0, _start=1970-01-01T00:00:00Z, _stop=2020-09-10T09:40:34.496818215Z, _time=1992-01-01T00:00:00Z, _value=2, _field=a, _measurement=mymeasurement}
{result=_result, table=1, _start=1970-01-01T00:00:00Z, _stop=2020-09-10T09:40:34.496818215Z, _time=1992-01-01T00:00:00Z, _value=2.5, _field=b, _measurement=mymeasurement}
{result=_result, table=1, _start=1970-01-01T00:00:00Z, _stop=2020-09-10T09:40:34.496818215Z, _time=1993-01-01T00:00:00Z, _value=3.5, _field=b, _measurement=mymeasurement}

Which essentially is:

  • 1991-01-01T00:00:00Z: a=1
  • 1992-01-01T00:00:00Z: a=2, b=2.5
  • 1993-01-01T00:00:00Z: b=3.5

Is there any way that I can only get all available fields for the first 2 timestamps?

The default way that Flux processes data from InfluxDB is one field at a time. To do what you want, use the fieldsAsCols() function to transform the data so that all of the fields for a measurement are in the same row.

import "influxdata/influxdb/v1"

from(bucket: "mybucket")
    |> range(start: 1970-01-01T00:00:00Z)
    |> filter(fn: (r) => r._measurement == "mymeasurement")
    |> v1.fieldsAsCols()
    |> limit(n: 2)
    |> yield()

Should do what you want

1 Like

Thank you! That does exactly what I want and is also much faster and simpler than the one-field-at-time approach.

I was sceptical whether I should use a “v1” function as it sounds like that would be deprecated and removed pretty soon.
But v1.fieldsAsCols() seems to be built on the regular pivot() function so it should be here to stay, right?