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

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

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.

Examples

SELECT * FROM "h2o_feet"

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

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

SELECT *::field FROM "h2o_feet"

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

SELECT * FROM "h2o_feet","h2o_pH"

SELECT * FROM noaa.."h2o_feet"

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:

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

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

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

SELECT /l/ FROM "h2o_feet" LIMIT 1

SELECT MEAN("degrees") FROM /temperature/

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.

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:

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

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

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

Merge behavior

InfluxQL merges series automatically.

Example

SELECT MEAN("water_level") FROM "h2o_feet"

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

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

Multiple statements

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

Examples

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

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