Add column to data if it does not exist

Fluxlang
1

Hello,

Is it possible to add a column with default value to data if it does not already exist?

I have tried the following approaches

fill(column: "columnName", value: 0.0)
fill column not found: missingColumn

map(fn: (r) => ({r with columnName: if exists r.columnName then r.columnName else 0.0}))
missing object properties (columnName)

2

@tkupari What version of InfluxDB/Flux are you using?

3

InfluxDB 2.0.0 Alpha 18
Flux 0.49.0

4

Thanks @tkupari. I can reproduce the error. From what I can tell, this should work but I may be missing something. You should submit an issue to the Flux repo.

5

Thanks for the verification @scott. Should they both work or is fill() meant only for existing columns?

6

@tkupari fill() only works with existing columns.

I talked with the Flux engineers and this appears to, in fact, be a bug… specifically with the typing system. The good news is that they’re in the process of refactoring the typing engine to be smarter and much more robust. The bad news is that it’s not a quick fix.

7

There is a workaround, although it’s not ideal. You could create two streams, one that filters data with the column and one that filters data without. Then create a union of the two streams:

missingColumnName = from(bucket: "example-bucket")
  |> range(start: -1h)
  |> filter(fn: (r) => not exists r.columnName)
  |> map(fn: (r) => ({ r with columnName: 0.0 }))

hasColumnName = from(bucket: "example-bucket")
  |> range(start: -1h)
  |> filter(fn: (r) => exists r.columnName)

union(tables: [missingColumnName, hasColumnName])

It’s not the most performant of queries, but it’s a temporary workaround for what you’re trying to do. Be sure to filter each stream to the specific dataset you want to add the column to. This will help to optimize the query.

8

Thanks @scott! I’m afraid this might not be feasible in our use case as we are doing calculations with 5 different values which could all be missing, and if I understood correctly it would require 10 streams. I think we can filter out the missing data for now.

Should I still submit an issue to the Flux repo?

9

No need to. It’s related to this existing issue: missing object properties (place) · Issue #2036 · influxdata/flux · GitHub

10

Please advise if there is a better solution for this problem or just this workaround.

11

Thanks in advance for all your time you are putting into supporting users!!

After years of annoying weird-name-entity-filters I finally managed to add friendly names in influxdb. Unfortunately, values recorded pre-friendly_name-tag do not have this “friendly_name” column.

I can merge the pre-friendly_name and post-friendly_name values from the same entity. But when I want to have several entities in one chart I cannot group in “entity x pre-friendly_name and post-friendly_name” and “entity y pre-friendly_name and post-friendly_name” to merge. The merge goes haywire as it merges all values.

So I went for the solution mentioned in this thread. Thank you very much again for this one. The Problem is:

  |> filter(fn: (r) => not exists r.friendly_name)
  |> map(fn: (r) => ({r with friendly_name: "Zi_kl_Ost_Temp"}))

does not create the column at all. Any idea why?

Thanks