Help with query needed

Hi all,
I need some help with a query. I’m using node-red to write utility data to InfluxDB. This works fine. The data is stored in the bucket Energymeter, and three tables are created for the fields gas, peak and offpeak.

var payload;
payload={
  bucket: 'EnergyMeter',
  precision: 's',
  data: [
    {
      measurement: 'Energy',
      fields: {
        Gas:msg.payload.Gas,
        Peak:msg.payload.Peak,
        OffPeak:msg.payload.OffPeak,
        },
    },
  ]
}
msg.payload=payload;
return msg;

Now I’m trying to query this information for the last 14 days and my basic flux knowledge is not enough to get the right results.
What I would like to get from the query is an array like below:

[0] ["Gas"="..."]["Peak"="..."]["OffPeak"="..."]   // Today
[1] ["Gas"="..."]["Peak"="..."]["OffPeak"="..."]   // Yesterday
[2] ["Gas"="..."]["Peak"="..."]["OffPeak"="..."]   // Day before Yesterday
[3] ["Gas"="..."]["Peak"="..."]["OffPeak"="..."]   // 3 days ago
 .
 .
 .
 .
 .
[14] ["Gas"="..."]["Peak"="..."]["OffPeak"="..."]  // 14 Days ago

Can anyone point me in the right direction for such a query.
Thx,
PPee

Hello @PPee,
I’m thinking that you want to use fieldsAsCols()
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/influxdb-schema/fieldsascols/

So you’d use: msg.payload[0].Gas to get the value from the annotated CSV output

This video might help you:

As an aside, findRecord() could also be potentially interesting
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/stream-table/findrecord/

Yeah, that would be the right function I think. Thx Anaisdg!

1 Like