Problem with pivot() in specifying rows and coloums

InfluxDB 2
1

Hi,

I have a problem with getting the correct aggregation / join / pivot of my data.
I know aggregation, join and pivot from relational dbs, excel or whatever but it seems that influxdb works somehow different.

Example of my base data:

light,category=toggle,floor=2nd,full_name=Office_Light_Desk_OnOff,groupaddress=1/2/207,host=telegraf,object=desk,room=Office,source=1.2.60 value=false 1757789306604644331

light,category=toggle,floor=2nd,full_name=Office_Light_Top_OnOff,groupaddress=1/2/200,host=telegraf,object=top,room=Office,source=0.0.254 value=false 1757790807432413619

light,category=toggle,floor=2nd,full_name=Office_Light_Door_OnOff,groupaddress=1/2/210,host=telegraf,object=door,room=Office,source=0.0.254 value=true 1757790807600401816
…

with the following flux lines

from(bucket: “sourceData”)
|> range(start: start)
|> filter(fn: (r) => r._measurement == “light” and r.category == “toggle” and r.room == “Office”)
|> aggregateWindow(every: aggtime, fn: last, createEmpty: true)
|> set(key: “_field”, value: “onoff”)
|> fill(usePrevious: true)
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)

I am getting the following result

,result,table,_start,_stop,_time,_measurement,category,floor,full_name,groupaddress,host,object,room,source,onoff

,0,2025-09-14T11:28:32.077162988Z,2025-09-15T11:28:32.077162988Z,2025-09-14T11:29:00Z,light,toggle,2nd,Office_Light_Top_OnOff,1/2/200,telegraf,top,Office,0.0.254,false

,1,2025-09-14T11:28:32.077162988Z,2025-09-15T11:28:32.077162988Z,2025-09-14T11:29:00Z,light,toggle,2nd,Office_Light_Desk_OnOff,1/2/207,telegraf,desk,Office,0.0.254,true

,2,2025-09-14T11:28:32.077162988Z,2025-09-15T11:28:32.077162988Z,2025-09-14T11:29:00Z,light,toggle,2nd,Office_Light_Door_OnOff,1/2/210,telegraf,door,Office,0.0.254,false

My intention to get is only 1 row per timestamp:

2025-09-14T11:28:32.077162988Z,2025-09-15T11:28:32.077162988Z,2025-09-14T11:29:00Z,light,toggle,2nd,Office_Light_Top_OnOff,1/2/200,telegraf,top,Office,0.0.254,false, Office_Light_Desk_OnOff,1/2/207,desk,true Office_Light_Door_OnOff,1/2/210,door,false

Normally in a pivot-table I would specify the time, floor and room in the rows and the other infos in the coloums. But in flux it seems to be different.

  1. Is it because I am getting 3 tables and I have to union() or join() the tables first?
  2. Any ideas how to realize it here?
2

@DJPicasso pivot() doesn’t remove columns from the group key, so rows are still grouped by tag values. That’s why you have separate tables/rows. If you don’t care about the different tag values and want everything in a single table with a single row per timestamp, you can “ungroup” (group by an empty column set) the data before you pivot. If you do this, the output table will only include a _time column and then columns for each of your fields.

from(bucket: "sourceData")
  |> range(start: start)
  |> filter(fn: (r) => r._measurement == "light" and r.category == "toggle" and r.room == "Office")
  |> aggregateWindow(every: aggtime, fn: last, createEmpty: true)
  |> set(key: "_field", value: "onoff")
  |> fill(usePrevious: true)
  |> group()
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
3

Hi @scott

thanks for your answer. But … I do not get the columns for each field.

With

|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)

I get only time and _field, nothing else.

table _time onoff

0 2025-09-14T15:13:00.000Z false
0 2025-09-14T15:14:00.000Z true
0 2025-09-14T15:15:00.000Z false

If I change that into

|> pivot(rowKey: [“_time”], columnKey: [“object”], valueColumn: “_value”)

I am getting also the columns

table _time top desk door

So partly what I want to get, but I specially need the “groupaddress” value, too!

table _time top groupaddress desk groupaddress door groupaddress
0 2025-09-14T15:13:00.000Z false 1/2/200 false 1/2/207 true 1/2/210
0 2025-09-14T15:14:00.000Z false 1/2/200 true 1/2/207 true 1/2/210
0 2025-09-14T15:15:00.000Z false 1/2/200 false 1/2/207 false 1/2/210

This is where I get into trouble and do not know how to pivot()

4

I am getting nearer

|> pivot(rowKey: [“_time”,“floor”,“room”,“groupaddress”,“full_name”], columnKey: [“object”], valueColumn: “_value”)

delivers

table time top groupaddress desk door room floor full_name
0 2025-09-14T15:13:00.000Z false 1/2/200 false true Office 2nd …
0 2025-09-14T15:14:00.000Z false 1/2/200 true true Office 2nd …

currently only the second and third “groupaddress” values missing

5

You actually set the value of the _field column to onoff. This essentially makes it so you only have one field.

Correct. For each unique value or combination of values in the column(s) you specify in your columnKey parameter, you will get a distinct column from the pivot operation.

Can you describe or show me the output you’re looking for from this query? I may be able to help get you there.

6

Hi scott,

even with that flux

from(bucket: "sourceData")
  |> range(start: start)
  |> filter(fn: (r) => r._measurement == "light" and r.category == "toggle" and r.room == "Office")
  |> aggregateWindow(every: aggtime, fn: last, createEmpty: true)
//  |> set(key: "_field", value: "onoff")
  |> fill(usePrevious: true)
  |> group()
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

I am only getting 3 columns:

table _time value

there is no difference, except the headline changing from “value” to “onoff”.

The flux:

|> pivot(rowKey: [“_time”], columnKey: [“object”], valueColumn: “_value”)

delivers the colums

table _time top desk door

based on the specified values in the tag “object”.

What I am looking for:

table _time top groupaddress desk groupaddress door groupaddress
0 2025-09-14T15:13:00.000Z false 1/2/200 false 1/2/207 true 1/2/210
0 2025-09-14T15:14:00.000Z false 1/2/200 true 1/2/207 true 1/2/210
0 2025-09-14T15:15:00.000Z false 1/2/200 false 1/2/207 false 1/2/210

I need the different groupaddresses that belong to the objects

groupaddress for desk (1/2/207)
groupaddress for top (1/2/200)
groupaddress for door (1/2/210)

7

The format you’re proposing is invalid. You can’t have multiple groupaddress columns. Column labels must be unique. You could potentially have three separate tables, one for each group address.

8

Hi @scott

many thanks for your help! You have 3 beers free :clinking_beer_mugs: :winking_face_with_tongue:

Is there any other chance to

  • probably rename the other 2 groupaddress columns before creating the pivot table?
  • transform the groupaddress columns in another way?

Because otherwise it doesn’t make sense - in my case - to create the pivot table.
I need to create 1 time-row with all object information or I have to leave it as it is and to find a solution with multiple tables.

9

What is the relationship between groupaddress, door/desk/top and the true/false value?

10

Hi @scott

I am collecting data from a KNX bus in a SmartHome.

In this example I was talking about the light data in my office.
I have 4 lights:

  • desk (wall over my desk)
  • top
  • door (wall near the door)
  • window (wall near the window)

The groupaddress is the address of the object which is somehow comparable to an IP-address.
With the groupaddress you can change the state of the object or fire an event.

E.g for the desk light there are 2 groupaddresses.
1st address (1/2/200) to switch on/off the light. Type is boolean because there a only 2 states.
2nd address (1/2/208) to set the dim brightness of the light. Type is float because there are values from 0 - 100 (%).

There could also be more addresses if you want to control colours or whatever for one light.
So if I send a “true” (or 1) on the bus with the address 1/2/200 the light over the desk will be switched on and if I send a 70 to the address 1/2/208 the light will be dimmed to 70%. A false (or 0) on 1/2/200 will switch off the light over the desk. The same with 0 (0%) on 1/2/208.

I am collecting all the bus data from all objects with a KNX_listener (telegraf), writing it into influxdb. I am getting round about 40.000 to 50.000 record per day for all objects that I am listening to.

Of course if there is a light, that can’t be dimmed there is only 1 address to switch on/off, the dimmed lights have (min.) 2 addresses.

The background for my pivot question is:

In a SmartHome very often you are working with so called scenarios/profiles.
It means e.g.:
You have a zone or room like my office with n lights, here 4 lights including 2 lights that can be dimmed.

In the timeframe the lights shall be switched in different ways:
6:00 - 8:00 only the top light shall be switched on if you enter into the office
8:00 - 10:00 light on the top (swiched on), light over the desk (swiched on, 100%), light at the window (switched on, 50%), light at the door (off)
10:00 - 16:00 light over the desk (on, 100%) all other off
16:00 - 20:00 all lights on, 100%
20:00 - 06:00 only light at the window (on, 50%)

To get the scene-info I would like to create 1 row with all the scene infos in it but therefore I have to get multiple columns with groupaddresses.
I was thinking about renaming the column to groupaddress_${object} during filter but currently I am not so familiar with flux. I am still at the beginning.
With sql-query it would be easy for me!

What I need:
There are 2 possibilities

1st
I need a “long row” with all infos for the current light scenario and all objects have multiple groupaddresses, object columns etc.

Infos I need:

  • All states of lights in one zone/room for a special timestamp
  • state of presence or movement within a zone or room where the lights are in (to get the info, that someone is in this zone/room)
  • position of roller shutter
  • infos about sun position (azimuth, altitude, sunrise etc.)
  • …

2nd possibility
I collect all the data for a special timestamp getting n tables

  • n tables for the different lights in a zone/room (here: my office)
  • table for state of presence / movement
  • table for sun informations
  • table for weather informations
  • table for position of roller shutters (can be n shutters based on the zone or room)
  • …

But getting all the tables for 1 scene I need to put all datas together, being responsible for the event (switch on the light, or switch on a special scene) and evaluate these data afterwards.

11

I think it’s the single row requirement that’s throwing me off. Is this to do with how you’re processing the query results? I feel like this requires a fairly non-standard data structure. With Flux, you could get something that looks like this:

_time top_1/2/200 desk_1/2/207 door_1/2/210
2025-09-14T15:13:00Z false false true
2025-09-14T15:14:00Z false true true
2025-09-14T15:15:00Z false false false

Where the column names are a combination of the object and the groupaddress. Would this work? If so, here’s how you’d do it:

from(bucket: "sourceData")
  |> range(start: start)
  |> filter(fn: (r) => r._measurement == "light" and r.category == "toggle" and r.room == "Office")
  |> aggregateWindow(every: aggtime, fn: last, createEmpty: true)
  |> fill(usePrevious: true)
  |> pivot(rowKey: ["_time"], columnKey: ["object", "groupaddress"], valueColumn: "_value")

However, if you have multiple fields, this may not work as intended.