Documentation

SELECT statement

This page documents an earlier version of InfluxDB. InfluxDB v2.5 is the latest stable version. View this page in the v2.5 documentation.

Use the SELECT statement to query data from a particular measurement or measurements.

Syntax

SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

Note: The SELECT statement requires a SELECT clause and a FROM clause.

SELECT clause

The SELECT clause supports several formats for specifying data:

  • SELECT * - Returns all fields and tags.
  • SELECT "<field_key>" - Returns a specific field.
  • SELECT "<field_key>","<field_key>" - Returns more than one field.
  • SELECT "<field_key>","<tag_key>" - Returns a specific field and tag. The SELECT clause must specify at least one field when it includes a tag.
  • SELECT "<field_key>"::field,"<tag_key>"::tag - Returns a specific field and tag. The ::[field | tag] syntax specifies the identifier’s type. Use this syntax to differentiate between field keys and tag keys with the same name.

Other supported features include:

Note: The SELECT statement cannot include an aggregate function and a non-aggregate function, field key, or tag key. For more information, see error about mixing aggregate and non-aggregate queries.

FROM clause

The SELECT clause specifies the measurement to query. This clause supports several formats for specifying a measurement(s):

  • FROM <measurement_name> - Returns data from a measurement.
  • FROM <measurement_name>,<measurement_name> - Returns data from more than one measurement.
  • FROM <database_name>.<retention_policy_name>.<measurement_name> - Returns data from a fully qualified measurement.
  • FROM <database_name>..<measurement_name> - Returns data from a measurement.

Quoting

Identifiers must be double quoted if they contain characters other than [A-z,0-9,_], begin with a digit, or are an InfluxQL keyword. While not always necessary, we recommend that you double quote identifiers.

Note: InfluxQL quoting guidelines differ from line protocol quoting guidelines. Please review the rules for single and double-quoting in queries.

Examples

Select all fields and tags from a measurement

Select specific tags and fields from a measurement

Select specific tags and fields from a measurement and provide their identifier type

Select all fields from a measurement

Select a specific field from a measurement and perform basic arithmetic

Select all data from more than one measurement

Select all data from a measurement in a particular database

Common issues with the SELECT statement

Selecting tag keys in the SELECT statement

A query requires at least one field key in the SELECT clause to return data. If the SELECT clause only includes a single tag key or several tag keys, the query returns an empty response.

Example

The following query returns no data because it specifies a single tag key (location) in the SELECT clause:

SELECT "location" FROM "h2o_feet"
> No results

To return any data associated with the location tag key, the query’s SELECT clause must include at least one field key (water_level):

SELECT "water_level","location" FROM "h2o_feet"

Output:

Name: h2o_feet

timewater_levellocation
2019-08-17T00:00:00Z8.1200000000coyote_creek
2019-08-17T00:00:00Z2.0640000000santa_monica
2019-08-17T 00:06:00Z8.0050000000coyote_creek
2019-08-17T00:06:00Z2.1160000000santa_monica
2019-08-17T00:12:00Z7.8870000000coyote_creek
2019-08-17T00:12:00Z2.0280000000santa_monica
2019-08-17T00:18:00Z7.7620000000coyote_creek
2019-08-17T00:18:00Z2.1260000000santa_monica

Regular expressions

InfluxQL supports using regular expressions when specifying:

Currently, InfluxQL does not support using regular expressions to match non-string field values in the WHERE clause, databases, and retention policies.

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

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 Golang’s 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 statement

Use a regular expression to specify measurements in the FROM clause

Data types and cast operations

The SELECT clause supports specifying a field’s type and basic cast operations with the :: syntax.

Data types

Field values can be floats, integers, strings, or booleans. The :: syntax allows users to specify the field’s type in a query.

Note: Generally, it is not necessary to specify the field value type in the SELECT clause. In most cases, InfluxDB rejects any writes that attempt to write a field value to a field that previously accepted field values of a different type.

It is possible for field value types to differ across shard groups. In these cases, it may be necessary to specify the field value type in the SELECT clause. Please see the Frequently Asked Questions document for more information on how InfluxDB handles field value type discrepancies.

Syntax

SELECT_clause <field_key>::<type> FROM_clause

type can be float, integer, string, or boolean. In most cases, InfluxDB returns no data if the field_key does not store data of the specified type. See Cast Operations for more information.

Example

SELECT "water_level"::float FROM "h2o_feet" LIMIT 4

Output:

Name: h2o_feet

timewater_level
2019-08-17T00:00:00Z8.1200000000
2019-08-17T00:00:00Z2.0640000000
2019-08-17T00:06:00Z8.0050000000
2019-08-17T00:06:00Z2.1160000000

The query returns values of the water_level field key that are floats.

Cast operations

The :: syntax allows users to perform basic cast operations in queries. Currently, InfluxDB supports casting field values from integers to floats or from floats to integers.

Syntax

SELECT_clause <field_key>::<type> FROM_clause

type can be float or integer.

InfluxDB returns no data if the query attempts to cast an integer or float to a string or boolean.

Examples

Cast float field values to integers

Cast float field values to strings (this functionality is not supported)

Merge behavior

InfluxQL merges series automatically.

Example

Merge behavior

Multiple statements

Separate multiple SELECT statements in a query with a semicolon (;).

Examples

In the InfluxQL shell:

SELECT MEAN("water_level") FROM "h2o_feet"; SELECT "water_level" FROM "h2o_feet" LIMIT 2

Output:

Name: h2o_feet

timemean
1970-01-01T00:00:00Z4.4419314021

Name: h2o_feet

timewater_level
2019-08-17T00:00:00Z8.12
2015-08-18T00:00:00Z2.064

With the InfluxDB API:

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "h2o_feet",
                    "columns": [
                        "time",
                        "mean"
                    ],
                    "values": [
                        [
                            "1970-01-01T00:00:00Z",
                            4.442107025822522
                        ]
                    ]
                }
            ]
        },
        {
            "statement_id": 1,
            "series": [
                {
                    "name": "h2o_feet",
                    "columns": [
                        "time",
                        "water_level"
                    ],
                    "values": [
                        [
                            "2015-08-18T00:00:00Z",
                            8.12
                        ],
                        [
                            "2015-08-18T00:00:00Z",
                            2.064
                        ]
                    ]
                }
            ]
        }
    ]
}

Was this page helpful?

Thank you for your feedback!


Set your InfluxDB URL

Upgrade to InfluxDB Cloud or InfluxDB 2.0!

InfluxDB Cloud and InfluxDB OSS 2.0 ready for production.