Operate on columns

Use the following common queries to operate on columns:

• Find and count unique values in a column
• Recalculate the _values column
• Calculate a new column

These examples use NOAA water sample data.

Find and count unique values in a column

Find and count the number of unique values in a specified column. The following examples find and count unique locations where data was collected.

Find unique values

This query:

• Uses group() to ungroup data and return results in a single table.
• Uses keep() and unique() to return unique values in the specified column.

from(bucket: "noaa")
    |> range(start: -30d)
    |> group()
    |> keep(columns: ["location"])
    |> unique(column: "location")

Example results

Count unique values

This query:

• Uses group() to ungroup data and return results in a single table.
• Uses keep(), unique(), and then count() to count the number of unique values.

from(bucket: "noaa")
    |> group()
    |> unique(column: "location")
    |> count(column: "location")

Example results

Recalculate the _values column

To recalculate the _value column, use the with operator in map() to overwrite the existing _value column.

The following query:

• Uses filter() to filter the average_temperature measurement.
• Uses map() to convert Fahrenheit temperature values into Celsius.

from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with _value: (float(v: r._value) - 32.0) * 5.0 / 9.0} ))

Calculate a new column

To use values in a row to calculate and add a new column, use map(). This example below converts temperature from Fahrenheit to Celsius and maps the Celsius value to a new celsius column.

The following query:

• Uses filter() to filter the average_temperature measurement.
• Uses map() to create a new column calculated from existing values in each row.

from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with celsius: (r._value - 32.0) * 5.0 / 9.0}))

Example results

queries