Question regarding empty field values

I have bunch of field keys in a measurement which do not have any values. For example

f1 f2

1 1

Is it possible to extract those values? In this case the blank f2 value?

I cannot find anything on web-search. Is it true that I cannot query on those values?

You have a few options when it comes to blank values. InfluxQL supports fill(<fill_options>) which replaces those blank values with the relevant fill_option. One of those options is none; a query with fill(none) reports no timestamp and no value for time intervals with no data.

My Question is more specific. What if I want to fetch the row in which the field value is blank? in the above example the second row.
Also, is fill function allowed to use without a function in projection?

You could try this:

select * from (select * from m fill(999999999)) where f2 = 999999999

The database doesn’t store nulls, so there’s no simple query for this, but if there’s a value you know will never occur, you can fill with it, and then query for that value.

Apologies, I am still trying to figure out this.
I understood what you mean to say but it doesn’t work.
Please see the example below:

You need the query and the subquery in order to fill the empty values and return them.

select * from (select * from m fill(999999999)) where f2 = 999999999

The outer select (main query) will filter the results to get you the correct results
select * from () where f2 = x

and the inner select (subquery) will find any empty values and fill them in with whatever you put in fill(x).
(select * from m fill(999999999))

Does that help?

Right, so my above screenshot does not fill empty values.

As you said,
select * from m fill(999999999) should have returned empty values with 999999999 but, it didn’t.

Filtering those values will be later right?
Am I doing anything wrong here?

No worries! I’m not sure I have it quite figured out either. It’s hard to replicate. Give me a little time to try to get a working example closer to your example.

1 Like

The short answer is that in 1.5 (most recent stable version), fill() works with aggregates, but not in simple queries like the one we’re trying.

However, if I use the nightly build (unstable), the fill() does work on a select * from m.

1 Like