How to merge results table into a single one

The default behavior of Flux is to return a table for each combination of tag values.

So if I run a query that does not apply any filter on certain tags, I’ll get multiple tables as a result. Sorting by _time will actually only sort inside each table.

How can I merge all the results into a single table sorted by _time and tags displayed as columns?

from(bucket: "traces")
          |> range(start: -1h)
          |> filter(fn: (r) => r["_measurement"] == "traces")
          |> drop(columns: ["_start", "_stop", "result", "table", "_measurement", "source"])
          |> pivot(
            rowKey:["_time"],
            columnKey: ["_field"],
            valueColumn: "_value"
          )
          |> sort(columns: ["_time"])

The traces bucket has some tags like “source”, “application” and “level” which all do get pivoted into a column but the overall result is split into multiple tables. I need all to be into a single one so that I can export to CSV and it’s already ordered.

Hi, you can use group() to group all records into a single table on output.

from(bucket: "traces")
          |> range(start: -1h)
          |> filter(fn: (r) => r["_measurement"] == "traces")
          |> drop(columns: ["_start", "_stop", "result", "table", "_measurement", "source"])
          |> pivot(
            rowKey:["_time"],
            columnKey: ["_field"],
            valueColumn: "_value"
          )
          |> sort(columns: ["_time"])
          // Adding group to end of query to merge all results into a single table for output
          |> group()
2 Likes

Jeez that was easy. I was looking at things like join or merge!

Thanks!

@Anaisdg just hours ago published this post that explains a lot about why this happens and how to do what you wanted to do: TL;DR InfluxDB Tech Tips — Aggregating across Tags or Fields and Ungrouping | InfluxData

1 Like