SELECT statement

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

• Syntax
• Examples
• Common issues
• Regular expressions
• Data types and cast operations
• Merge behavior
• Multiple statements

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:

• Functions
• Basic cast operations
• Regular expressions

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 * FROM "h2o_feet"

Output:

Name: h2o_feet

The data above is a partial listing of the query output, as the result set is quite large. The query selects all fields and tags from the h2o_feet measurement.

Select specific tags and fields from a measurement

SELECT "level description","location","water_level" FROM "h2o_feet"

Output:

Name: h2o_feet

The query selects the level description field, the location tag, and the water_level field.

Note: The SELECT clause must specify at least one field when it includes a tag.

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

SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"

Output:

Name: h2o_feet

The query selects the level description field, the location tag, and the water_level field from the h2o_feet measurement. The ::[field | tag] syntax specifies if the identifier is a field or tag. Use ::[field | tag] to differentiate between an identical field key and tag key. That syntax is not required for most use cases.

Select all fields from a measurement

SELECT *::field FROM "h2o_feet"

Output:

Name: h2o_feet

The query selects all fields from the h2o_feet measurement. The SELECT clause supports combining the * syntax with the :: syntax.

Select a specific field from a measurement and perform basic arithmetic

SELECT ("water_level" * 2) + 4 FROM "h2o_feet"

Output:

Name: h2o_feet

The query multiplies water_level’s field values by two and adds four to those values.

Note: InfluxDB follows the standard order of operations. See InfluxQL mathematical operators for more on supported operators.

Select all data from more than one measurement

SELECT * FROM "h2o_feet","h2o_pH"

Output:

Name: h2o_feet

Name: h2o_pH

The query selects all fields and tags from two measurements: h2o_feet and h2o_pH. Separate multiple measurements with a comma (,).

Select all data from a measurement in a particular database

SELECT * FROM noaa.."h2o_feet"

Output:

Name: h2o_feet

The query selects data from the h2o_feet measurement in the noaa database. The .. indicates the DEFAULT retention policy for the specified 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

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

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
• Cast operations

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

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

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

Output:

Name: h2o_feet

The query returns the integer form of water_level’s float field values.

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

SELECT "water_level"::string FROM "h2o_feet" LIMIT 4
> No results

The query returns no data as casting a float field value to a string is not yet supported.

Merge behavior

InfluxQL merges series automatically.

Example

Merge behavior

The h2o_feet measurement in the noaa is part of two series. The first series is made up of the h2o_feet measurement and the location = coyote_creek tag. The second series is made of up the h2o_feet measurement and the location = santa_monica tag.

The following query automatically merges those two series when it calculates the average water_level using the MEAN() function:

SELECT MEAN("water_level") FROM "h2o_feet"

Output:

Name: h2o_feet

If you want the average water_level for the first series only, specify the relevant tag in the WHERE clause:

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'coyote_creek'

Output:

Name: h2o_feet

If you want the average water_level for each individual series, include a GROUP BY clause:

SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"

Output:

name: h2o_feet
tags: location=coyote_creek

name: h2o_feet
tags: location=santa_monica

Multiple statements

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

Examples

InfluxQL shell InfluxDB API

In the InfluxQL shell:

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

Output:

Name: h2o_feet

Name: h2o_feet

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
                        ]
                    ]
                }
            ]
        }
    ]
}