All Products
Search

This Product

    Time Series Database:Regular Expressions

    Document Center

    Time Series Database:Regular Expressions

    Last Updated:Aug 06, 2020

    InfluxQL allows you to use regular expressions when you specify the following objects in your query:

    • Field keys and tag keys in the SELECT clause
    • Measurements in the FROM clause
    • Tag values and the field values of the STRING type in the WHERE clause
    • Tag keys in the GROUP BY clause

    InfluxQL does not allow you to use regular expressions to match field values whose data type is not STRING in the WHERE clauses, databases, and retention policies.

    Notes: Regular expression comparisons consume more computing resources than string comparisons. The queries that use regular expressions have lower performance than the queries that do not use regular expressions.

    Syntax

    1. SELECT /<regular_expression_field_key>/ FROM /<regular_expression_measurement>/ WHERE [<tag_key> <operator> /<regular_expression_tag_value>/ | <field_key> <operator> /<regular_expression_field_value>/] GROUP BY /<regular_expression_tag_key>/

    Description

    Regular expressions are placed between forward slashes (/). The regular expressions follow the regular expression syntax of Golang.

    The following table lists the supported operators.

    =~ Match
    ! ~ Not match

    Examples

    Use a regular expression in the SELECT clause to specify field keys and tag keys

    1. > SELECT /l/ FROM "h2o_feet" LIMIT 1
    3. name: h2o_feet
    4. time                   level description      location       water_level
    5. ----                   -----------------      --------       -----------
    6. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12

    The query returns all field keys and tag keys that contain the character l. The regular expression in the SELECT clause must match at least one field key so that the query can return the results for the tag keys that match the regular expression.

    No syntax is available to distinguish between the regular expressions for field keys and those for tag keys in the SELECT clause. The syntax /<regular_expression>/::[field | tag] is not supported.

    Use a regular expression and a function in the SELECT clause to specify field keys

    1. > SELECT DISTINCT(/level/) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z'
    3. name: h2o_feet
    4. time                   distinct_level description   distinct_water_level
    5. ----                   --------------------------   --------------------
    6. 2015-08-18T00:00:00Z   below 3 feet                 2.064
    7. 2015-08-18T00:00:00Z                                2.116
    8. 2015-08-18T00:00:00Z                                2.028

    The query uses an InfluxQL function and returns the unique field values for the field keys that contain level.

    Use a regular expression in the FROM clause to specify measurements

    1. > SELECT MEAN("degrees") FROM /temperature/
    3. name: average_temperature
    4. time                    mean
    5. ----                    ----
    6. 1970-01-01T00:00:00Z   79.98472932232272
    8. name: h2o_temperature
    9. time                    mean
    10. ----                    ----
    11. 1970-01-01T00:00:00Z   64.98872722506226

    The query uses an InfluxQL function to calculate the average value of degrees for the measurements whose names contain temperature in the NOAA_water_database database.

    Use a regular expression in the WHERE clause to specify tag values

    1. > SELECT MEAN(water_level) FROM "h2o_feet" WHERE "location" =~ /[m]/ AND "water_level" > 3
    3. name: h2o_feet
    4. time                   mean
    5. ----                   ----
    6. 1970-01-01T00:00:00Z   4.47155532049926

    The query uses an InfluxQL function to calculate the average field value of the water_level field key based on the specified conditions. The specified conditions are that the tag value of location includes m and the field value of water_level is greater than three.

    Use a regular expression in the WHERE clause to specify tag keys that does not have tag values

    1. > SELECT * FROM "h2o_feet" WHERE "location" !~ /./
    2. >

    The query returns the data that meets the specified condition from the h2o_feet measurement. The specified condition is that the location tag key does not have tag values. The query returns no data, because each point has a tag value for the location tag key in the NOAA_water_database database.

    You can run queries to obtain the same results if you remove the regular expression. For more information, see the “FAQ” topic.

    Use a regular expression in the WHERE clause to specify tag keys that have tag values

    1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" =~ /./
    3. name: h2o_feet
    4. time                   mean
    5. ----                   ----
    6. 1970-01-01T00:00:00Z   4.442107025822523

    The query uses an InfluxQL function to calculate the average of the water_level field values that meet the specified condition. The specified condition is that the corresponding tag key location has tag values.

    Use a regular expression in the WHERE clause to specify field values

    1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND "level description" =~ /between/
    3. name: h2o_feet
    4. time                   mean
    5. ----                   ----
    6. 1970-01-01T00:00:00Z   4.47155532049926

    The query uses an InfluxQL function to calculate the average of the water_level field values that meet the specified condition. The specified condition is that the corresponding field values of the level description field key include between.

    Use a regular expression in the GROUP BY clause to specify tag keys

    1. > SELECT FIRST("index") FROM "h2o_quality" GROUP BY /l/
    3. name: h2o_quality
    4. tags: location=coyote_creek
    5. time                   first
    6. ----                   -----
    7. 2015-08-18T00:00:00Z   41
    9. name: h2o_quality
    10. tags: location=santa_monica
    11. time                   first
    12. ----                   -----
    13. 2015-08-18T00:00:00Z   99

    The query uses an InfluxQL function to obtain the first value of index for each tag key that contains the l character.


    InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.