How to query from an Influx database with an absent field?

Question

I have a measurement gathered by telegraf. It has following structure:

name: smart_device

fieldKey    fieldType
--------    ---------
exit_status integer
health_ok   boolean
read_error_rate integer
seek_error_rate integer
temp_c      integer
udma_crc_errors integer

When I query this database I can do this:

> select  * from smart_device where  "health_ok" = true limit 1
name: smart_device
time            capacity    device  enabled exit_status health_ok   host    model           read_error_rate seek_error_rate serial_no   temp_c  udma_crc_errors wwn
----            --------    ------  ------- ----------- ---------   ----    -----           --------------- --------------- ---------   ------  --------------- ---
15337409500 2000398934016   sda Enabled     0           true        osd21   Hitachi HDS722020ALA330    0        0       JK11A4B8JR2EGW  38  0       5000cca222e6384f

and this:

> select  * from smart_device limit 1
name: smart_device
time            capacity    device  enabled exit_status health_ok   host    model   read_error_rate seek_error_rate serial_no   temp_c  udma_crc_errors wwn
----            --------    ------  ------- ----------- ---------   ----    -----   --------------- --------------- ---------   ------  --------------- ---
1533046990                   sda            0                      osd21

But when I try to filter out records with empty health_ok, I get empty output:

> select  * from smart_device where "health_ok"!= true 
>

How can I select measurements with empty (no? null?) health_ok?

m.ghoreshi · Accepted Answer · 2018-10-17T12:45:38.767

Unfortunately there is currently no way to do this using InfluxQL. InfluxDB is a form of document oriented database; it means rows of a measurement can have different schema. Therefore, there is no a concept of null for a field of a row; actually this row dose not have the field. for example suppose there are 4 rows in the measurement cost

> select * from cost
name: cost
time                isok type value
----                ---- ---- -----
1533970927859614000 true 1    100
1533970938243629700 true 2    101
1533970949371761100      3    103
1533970961571703900      2    104

As you can see, there are two rows with isok=true and two rows which have no field named isok; so there is only one way to select the time of rows which have the isok field with this query:

> select isok from cost
name: cost
time                isok
----                ----
1533970927859614000 true
1533970938243629700 true

Since InfluxQL currently dose not support subquery in where clause, therefor there is no way to query for rows with no isok field (If InfluxDB supports this type of query, you can query like this SELECT * FROM cost WHERE time NOT IN (SELECT isok FROM cost))

George Shuklin · Answer 2 · 2018-08-14T15:30:41.610

It's not exactly the answer for the original question, but I found a special trick for Kapacitor.

If this query has been executed by kapacitor, it (kapacitor) has a special node default which allows to add missing fields/tags with some value.

For the health_ok query it will look like this (tickscript):

var data = stream
    |from()
      .measurement('smart_device')
        |default()   
           .field('health_ok', FALSE)

This allows to assume that if health_ok is missed, it is FALSE.

How to query from an Influx database with an absent field?

2 Answers2