Query Filter

In IoTDB query statements, two filter conditions, time filter and value filter, are supported.

The supported operators are as follows:

• Comparison operators: greater than (>), greater than or equal ( >=), equal ( = or ==), not equal ( != or <>), less than or equal ( <=), less than ( <).
• Logical operators: and ( AND or & or &&), or ( OR or | or ||), not ( NOT or !).
• Range contains operator: contains ( IN ).
• String matches operator: LIKE, REGEXP.

Time Filter

Use time filters to filter data for a specific time range. For supported formats of timestamps, please refer to Timestamp .

An example is as follows:

1. Select data with timestamp greater than 2022-01-01T00:05:00.000:

   select s1 from root.sg1.d1 where time > 2022-01-01T00:05:00.000;

2. Select data with timestamp equal to 2022-01-01T00:05:00.000:

   select s1 from root.sg1.d1 where time = 2022-01-01T00:05:00.000;

3. Select the data in the time interval [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000):

   select s1 from root.sg1.d1 where time >= 2022-01-01T00:05:00.000 and time < 2017-11-01T00:12:00.000;

Note: In the above example, time can also be written as timestamp.

Value Filter

Use value filters to filter data whose data values meet certain criteria. Allow to use a time series not selected in the select clause as a value filter.

An example is as follows:

1. Select data with a value greater than 36.5:

   select temperature from root.sg1.d1 where temperature > 36.5;

2. Select data with value equal to true:

   select status from root.sg1.d1 where status = true;

3. Select data for the interval [36.5,40] or not:

   select temperature from root.sg1.d1 where temperature between 36.5 and 40;

   select temperature from root.sg1.d1 where temperature not between 36.5 and 40;

4. Select data with values within a specific range:

   select code from root.sg1.d1 where code in ('200', '300', '400', '500');

5. Select data with values outside a certain range:

   select code from root.sg1.d1 where code not in ('200', '300', '400', '500');

6. Select data with values is null:

   select code from root.sg1.d1 where temperature is null;

7. Select data with values is not null:

   select code from root.sg1.d1 where temperature is not null;

Fuzzy Query

Fuzzy query is divided into Like statement and Regexp statement, both of which can support fuzzy matching of TEXT type data.

Like statement:

Fuzzy matching using Like

In the value filter condition, for TEXT type data, use Like and Regexp operators to perform fuzzy matching on data.

Matching rules:

• The percentage (%) wildcard matches any string of zero or more characters.
• The underscore (_) wildcard matches any single character.

Example 1: Query data containing 'cc' in value under root.sg.d1.

IoTDB> select * from root.sg.d1 where value like '%cc%'
+-----------------------------+----------------+
|                         Time|root.sg.d1.value|
+-----------------------------+----------------+
|2017-11-01T00:00:00.000+08:00|        aabbccdd| 
|2017-11-01T00:00:01.000+08:00|              cc|
+-----------------------------+----------------+
Total line number = 2
It costs 0.002s

Example 2: Query data that consists of 3 characters and the second character is 'b' in value under root.sg.d1.

IoTDB> select * from root.sg.device where value like '_b_'
+-----------------------------+----------------+
|                         Time|root.sg.d1.value|
+-----------------------------+----------------+
|2017-11-01T00:00:02.000+08:00|             abc| 
+-----------------------------+----------------+
Total line number = 1
It costs 0.002s

Fuzzy matching using Regexp

The filter conditions that need to be passed in are regular expressions in the Java standard library style.

Examples of common regular matching:

All characters with a length of 3-20: ^.{3,20}$
Uppercase english characters: ^[A-Z]+$
Numbers and English characters: ^[A-Za-z0-9]+$
Beginning with a: ^a.*

Example 1: Query a string composed of 26 English characters for the value under root.sg.d1

IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
+-----------------------------+----------------+
|                         Time|root.sg.d1.value|
+-----------------------------+----------------+
|2017-11-01T00:00:00.000+08:00|        aabbccdd| 
|2017-11-01T00:00:01.000+08:00|              cc|
+-----------------------------+----------------+
Total line number = 2
It costs 0.002s

Example 2: Query root.sg.d1 where the value value is a string composed of 26 lowercase English characters and the time is greater than 100

IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100
+-----------------------------+----------------+
|                         Time|root.sg.d1.value|
+-----------------------------+----------------+
|2017-11-01T00:00:00.000+08:00|        aabbccdd| 
|2017-11-01T00:00:01.000+08:00|              cc|
+-----------------------------+----------------+
Total line number = 2
It costs 0.002s