I have bunch of field keys in a measurement which do not have any values. For example
f1 f2
1 1
2
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.
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.
