Use regular expressions in InfluxQL queries - Time Series Database

InfluxQL supports regular expressions for pattern matching in queries. Use them to match field keys, tag keys, measurements, tag values, and string field values without listing every name explicitly.

Note

Regular expression comparisons consume more computing resources than string comparisons. Queries with regular expressions have lower performance than those without.

Supported contexts

Regular expressions work in the following clauses:

Clause	Applicable to
`SELECT`	Field keys and tag keys
`FROM`	Measurements
`WHERE`	Tag values; field values of the STRING data type
`GROUP BY`	Tag keys

Limitations: Regular expressions cannot match non-string field values in WHERE clauses, databases, or retention policies.

Syntax

Wrap regular expressions in forward slashes (/):

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

InfluxQL uses Go regular expression syntax.

Operators

Operator	Meaning
`=~`	Match
`!~`	Not match

Examples

Select field keys and tag keys matching a pattern

> SELECT /l/ FROM "h2o_feet" LIMIT 1

name: h2o_feet
time                   level description      location       water_level
----                   -----------------      --------       -----------
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12

This query returns all field keys and tag keys whose names contain l. The regular expression in the SELECT clause must match at least one field key for the query to return tag key results.

Note that the /<regular_expression>/::[field | tag] syntax for distinguishing between field and tag key regexes is not supported.

Select field keys matching a pattern using a function

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

name: h2o_feet
time                   distinct_level description   distinct_water_level
----                   --------------------------   --------------------
2015-08-18T00:00:00Z   below 3 feet                 2.064
2015-08-18T00:00:00Z                                2.116
2015-08-18T00:00:00Z                                2.028

This query returns the unique values for all field keys whose names contain level.

Select measurements matching a pattern

> SELECT MEAN("degrees") FROM /temperature/

name: average_temperature
time                    mean
----                    ----
1970-01-01T00:00:00Z   79.98472932232272

name: h2o_temperature
time                    mean
----                    ----
1970-01-01T00:00:00Z   64.98872722506226

This query calculates the mean value of degrees across all measurements in the NOAA_water_database whose names contain temperature.

Filter by tag values matching a pattern

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

name: h2o_feet
time                   mean
----                   ----
1970-01-01T00:00:00Z   4.47155532049926

This query calculates the mean water_level for data points where the location tag value contains m and water_level is greater than 3.

Filter for tag keys with no tag values

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

This query returns data from h2o_feet where the location tag has no value. The query returns no results because every point in NOAA_water_database has a value for the location tag.

You can achieve the same results without using a regular expression. For more information, see the FAQ topic.

Filter for tag keys that have tag values

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

name: h2o_feet
time                   mean
----                   ----
1970-01-01T00:00:00Z   4.442107025822523

This query calculates the mean water_level for data points where the location tag has any value.

Filter by string field values matching a pattern

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

name: h2o_feet
time                   mean
----                   ----
1970-01-01T00:00:00Z   4.47155532049926

This query calculates the mean water_level for data points in santa_monica where the level description field value contains between.

Group results by tag keys matching a pattern

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

name: h2o_quality
tags: location=coyote_creek
time                   first
----                   -----
2015-08-18T00:00:00Z   41

name: h2o_quality
tags: location=santa_monica
time                   first
----                   -----
2015-08-18T00:00:00Z   99

This query returns the first value of index grouped by all tag keys whose names contain l.

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