All Products
Search
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
  2. name: h2o_feet
  3. time level description location water_level
  4. ---- ----------------- -------- -----------
  5. 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'
  2. name: h2o_feet
  3. time distinct_level description distinct_water_level
  4. ---- -------------------------- --------------------
  5. 2015-08-18T00:00:00Z below 3 feet 2.064
  6. 2015-08-18T00:00:00Z 2.116
  7. 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/
  2. name: average_temperature
  3. time mean
  4. ---- ----
  5. 1970-01-01T00:00:00Z 79.98472932232272
  6. name: h2o_temperature
  7. time mean
  8. ---- ----
  9. 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
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 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" =~ /./
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 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/
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 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/
  2. name: h2o_quality
  3. tags: location=coyote_creek
  4. time first
  5. ---- -----
  6. 2015-08-18T00:00:00Z 41
  7. name: h2o_quality
  8. tags: location=santa_monica
  9. time first
  10. ---- -----
  11. 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®.