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
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
> 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
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
> 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
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
> 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
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
> 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
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
> SELECT * FROM "h2o_feet" WHERE "location" !~ /./
>
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
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" =~ /./
name: h2o_feet
time mean
---- ----
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
> 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
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
> 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
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®.