Regular expressions

InfluxQL supports using regular expressions when specifying:

• field keys and tag keys in the SELECT clause.
• measurements in the FROM clause.
• tag values and string field values in the WHERE clause.
• tag keys in the GROUP BY clause

Regular expressions in InfluxQL only support string comparisons and can only evaluate fields with string values.

Note: Regular expression comparisons are more computationally intensive than exact string comparisons. Queries with regular expressions are not as performant as those without.

• Syntax
• Examples

Syntax

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>/

Regular expressions are surrounded by / characters and use the Go regular expression syntax.

Supported operators

=~: matches against !~: doesn’t match against

Examples

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

SELECT /l/ FROM "h2o_feet" LIMIT 1

Output:

Name: h2o_feet

The query selects all field keys and tag keys that include an l. Note that the regular expression in the SELECT clause must match at least one field key in order to return results for a tag key that matches the regular expression.

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

Use a regular expression to specify field keys and tag keys in function arguments

SELECT MAX(/_level/) FROM "h2o_feet" LIMIT 1

Output:

Name: h2o_feet

This query uses the InfluxQL MAX() selector function to find the greatest field value out of all field keys that match the regular expression.

Use a regular expression to specify measurements in the FROM clause

SELECT MEAN("degrees") FROM /temperature/

Output:

Name: average_temperature

Name: h2o_feet

This query uses the InfluxQL MEAN() function to calculate the average degrees for every measurement in the [NOAA sample data] that contains the word temperature.

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

SELECT MEAN(water_level) FROM "h2o_feet" WHERE "location" =~ /[m]/ AND "water_level" > 3

Output:

Name: h2o_feet

This query uses the InfluxQL MEAN() function to calculate the average water_level where the tag value of location includes an m and water_level is greater than three.

Use a regular expression to specify a tag with no value in the WHERE clause

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

The query selects all data from the h2o_feet measurement where the location tag has no value. Every data point in the NOAA water sample data has a tag value for location. It’s possible to perform this same query without a regular expression. See the Frequently Asked Questions document for more information.

Use a regular expression to specify a tag with a value in the WHERE clause

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" =~ /./

Output:

Name: h2o_feet

This query uses the InfluxQL MEAN() function to calculate the average water_level across all data with a tag value for location.

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

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND "level description" =~ /between/

Output:

Name: h2o_feet

This query uses the InfluxQL MEAN() function to calculate the average water_level for all data where the field value of level description includes the word between.

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

SELECT FIRST("index") FROM "h2o_quality" GROUP BY /l/

Output:

name: h2o_quality
tags: location=coyote_creek

name: h2o_quality
tags: location=santa_monica

This query uses the InfluxQL FIRST() function

to select the first value of index for every tag that includes the letter l in its tag key.