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 ( <).
  • Range contains operator: contains ( IN ).
  • Logical operators: and ( AND or & or &&), or ( OR or | or ||), not ( NOT or !).

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:

1. 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:

1. 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):

1. 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:

1. select temperature from root.sg1.d1 where temperature > 36.5; 2. Select data with value equal to true:

1. select status from root.sg1.d1 where status = true; 3. Select data for the interval (36.5,40]:

1. select temperature from root.sg1.d1 where temperature > 36.5 and temperature < 40; 4. Select data with values within a specific range:

1. select code from root.sg1.d1 where code in ('200', '300', '400', '500'); 5. Select data with values outside a certain range:

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

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.

1. IoTDB> select * from root.sg.d1 where value like '%cc%'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:00.000+08:00|        aabbccdd|
6. |2017-11-01T00:00:01.000+08:00|              cc|
7. +-----------------------------+----------------+
8. Total line number = 2
9. 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.

1. IoTDB> select * from root.sg.device where value like '_b_'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:02.000+08:00|             abc|
6. +-----------------------------+----------------+
7. Total line number = 1
8. 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:

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

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

1. IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:00.000+08:00|        aabbccdd|
6. |2017-11-01T00:00:01.000+08:00|              cc|
7. +-----------------------------+----------------+
8. Total line number = 2
9. 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

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