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.

1. from(bucket: "noaa")
2. |> range(start: -30d)
3. |> group()
4. |> keep(columns: ["location"])
5. |> 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.

1. from(bucket: "noaa")
2. |> group()
3. |> unique(column: "location")
4. |> 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.

2. from(bucket: "noaa")
3. |> filter(fn: (r) => r._measurement == "average_temperature")
4. |> range(start: -30d)
5. |> 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.

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

Example results

queries