Skip to main content

Query Filter

About 3 min

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

Copyright © 2024 The Apache Software Foundation.
Apache and the Apache feather logo are trademarks of The Apache Software Foundation

Have a question? Connect with us on QQ, WeChat, or Slack. Join the community now.

We use Google Analytics to collect anonymous, aggregated usage information.