19

If my data (conceptually) is: 

#  a b c 
  -------
1  1   1
2  1 1 0
3  1 0 1

Then in legacy SQL language, the statement would be: 

select * from table where b is null

I cannot find a similar condition within the InfluxDB Query Language documentation.

I am working with data where there is optionally a numeric value in a column, and I want to select records where this column is empty/null. Since these are integers, they appear not to work with the matching regexes at all, so something like where !~ /.*/ is out.

glasnt
  • 2,865
  • 5
  • 35
  • 55
  • 1
    It's still [not possible to search for NULLs in InfluxDB](https://community.influxdata.com/t/search-non-existent-field-values/2035). – Dan Dascalescu Jan 03 '18 at 06:47

4 Answers4

14

InfluxDB doesn' understand NULL and will show error if use is null or is not null in the query. In order to find something which is like null we need to look for empty space i.e. use empty single quotes as 

SELECT * FROM service_detail where username != ''
Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Avis
  • 2,197
  • 18
  • 28
  • 3
    This works for searching for *non*-NULL values. To search for NULL, you can't say `username = ''`, because that's the empty string, which is different from that field not having a value at all. – Dan Dascalescu Jan 03 '18 at 06:47
  • @DanDascalescu what you say is true for *fields*. However, for *tags*, doing `WHERE some_tag = ''` *will* match rows for which the tag has no value. (The tag value will still be returned as `null` in the JSON response from the `/query` API, though, not as the empty string, because internally-consistent type systems are for pansies.) – Mark Amery Feb 05 '18 at 14:17
  • Guys my answer is an extension of what @glasnt spoke about, please understand the context and then judge to answers which are helping to get the problem solved. – Avis Feb 05 '18 at 18:46
  • This isn't working for me. I have a field key called "type", and I need to select all the rows where there's nothing in "type". `Select * from ... where type=''` returns nothing. – OZ1SEJ Feb 03 '21 at 11:00
10

You cannot search for nulls in InfluxDB <0.9. You will not be able to insert nulls in Influx >=0.9

glasnt
  • 2,865
  • 5
  • 35
  • 55
  • 11
    While you can't insert nulls in InfluxDB any more, [queries still return nulls](https://github.com/influxdata/docs.influxdata.com/issues/717), but you can't query *by* null (e.g. IS NULL or IS NOT NULL). – Dan Dascalescu Sep 26 '16 at 18:50
  • 1
    SO - this is almost six years ago now... Is this still the case? If so, how come this is still a thing? – OZ1SEJ Feb 04 '21 at 12:19
1

For fields where there exists at least one "invalid" value (for example, a negative size in bytes) you can create a query which allows you to find rows with missing data, without modifying the stored data.

I have a metric with 5 fields: mac, win, win64, linux, and linux64, not every field is filled in in every row, and on occasion a row will not be added due to it having no data available at the time.

By first querying the data with a fill() clause set to my invalid value: -1 in a subquery, I can then wrap that in an outer query to find either rows which are missing at least one column (using OR between WHERE expressions) or rows with no data at all (using AND between WHERE expressions).

The subquery looks like this:

SELECT count(*) FROM "firefox" GROUP BY time(1d) fill(-1)

That gives me all of my rows (there's one per day) with a 1 (the count of the occurrences of that field for the day) or a -1 (missing) as the value returned for each field.

I can then choose the rows that have no data from that with an outer query like this (note in this case the returned fields are all -1 and therefore uninteresting and can be hidden in your visualizer, like Grafana):

SELECT * from (_INNER_QUERY_HERE_) WHERE count_linux = -1 AND count_linux64 = -1 AND count_mac = -1 AND count_win = -1 AND count_win64 = -1;

Or I can choose rows with at least one missing field like this:

SELECT * from (_INNER_QUERY_HERE_) WHERE count_linux = -1 OR count_linux64 = -1 OR count_mac = -1 OR count_win = -1 OR count_win64 = -1;

There is still room for improvement though, you have to specify the field names in the outer query manually whereas something like WHERE * = -1 would be much nicer. Also depending on the size of your data this query will be SLOOOOOOW, and filtering by time is very confusing when you use nested queries. Obviously it'd be nicer if the influx folks just added is null or not null or some similar syntax to influxql, but as has been linked above they don't seem too interested in doing so.

Camden Narzt
  • 2,271
  • 1
  • 23
  • 42
1

In Flux queries of OSS 2.0, I had the same problem -> cannot search for "null" tags Steps:

if you search for "null"

|> filter(fn: (r) => r["_measurement"] == "my_measurement" and r.my_tag == null)

=> Error

if you search for ""

|> filter(fn: (r) => r["_measurement"] == "my_measurement" and r.my_tag == "")

=> you get no results

but using regex searching for empty value:

|> filter(fn: (r) => r["_measurement"] == "my_measurement" and r.my_tag =~ /^$/)

=> it works!

Michael
  • 791
  • 9
  • 9