Documentation

SELECT statement

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:

  • 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:

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

See regular expressions for more information.

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!


The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered

InfluxDB Cloud powered by TSM