Influxdata
/
docs-v2
mirror of https://github.com/influxdata/docs-v2.git
1
0
Fork 0
Code Issues Projects Releases Wiki Activity
docs-v2/content/shared/influxdb-v2/query-data/influxql/explore-data/select.md

21 KiB
Raw Permalink Blame History

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 %}} Note: The SELECT statement requires a SELECT clause and a FROM clause. {{% /note %}}

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 %}} 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. {{% /note %}}

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 %}} Note: InfluxQL quoting guidelines differ from line protocol quoting guidelines. Please review the rules for single and double-quoting in queries. {{% /note %}}

Examples

{{< expand-wrapper >}} {{% expand "Select all fields and tags from a measurement" %}}

SELECT * FROM "h2o_feet"

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description location water_level
2019-08-17T00:00:00Z below 3 feet santa_monica 2.0640000000
2019-08-17T00:00:00Z between 6 and 9 feet coyote_creek 8.1200000000
2019-08-17T00:06:00Z below 3 feet santa_monica 2.1160000000
2019- 08-17T00:06:00Z between 6 and 9 feet coyote_creek 8.0050000000
2019-08-17T00:12:00Z below 3 feet santa_monica 2.0280000000
2019-08-17T00:12:00Z between 6 and 9 feet coyote_creek 7.8870000000
2019-08-17T00:18:00Z below 3 feet santa_monica 2.1260000000

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.

{{% /expand %}}

{{% expand "Select specific tags and fields from a measurement" %}}

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

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description location water_level
2019-08-17T00:00:00Z below 3 feet santa_monica 2.0640000000
2019-08-17T00:00:00Z between 6 and 9 feet coyote_creek 8.1200000000

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

{{% note %}} Note: The SELECT clause must specify at least one field when it includes a tag. {{% /note %}}

{{% /expand %}}

{{% expand "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: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description location water_level
2019-08-17T00:24:00Z between 6 and 9 feet coyote_creek 7.6350000000
2019-08-17T00:30:00Z below 3 feet santa_monica 2.0510000000
2019-08-17T00:30:00Z between 6 and 9 feet coyote_creek 7.5000000000
2019-08-17T00:36:00Z below 3 feet santa_monica 2.0670000000
2019-08-17T00:36:00Z between 6 and 9 feet coyote_creek 7.3720000000
2019-08-17T00:42:00Z below 3 feet santa_monica 2.0570000000

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.

{{% /expand %}}

{{% expand "Select all fields from a measurement" %}}

SELECT *::field FROM "h2o_feet"

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description water_level
2019-08-17T00:00:00Z below 3 feet 2.0640000000
2019-08-17T00:00:00Z between 6 and 9 feet 8.1200000000
2019-08-17T00:06:00Z below 3 feet 2.1160000000
2019-08-17T00:06:00Z between 6 and 9 feet 8.0050000000
2019-08-17T00:12:00Z below 3 feet 2.0280000000
2019-08-17T00:12:00Z between 6 and 9 feet 7.8870000000

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

{{% /expand %}}

{{% expand "Select a specific field from a measurement and perform basic arithmetic" %}}

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

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time water_level
2019-08-17T00:00:00Z 20.2400000000
2019-08-17T00:00:00Z 8.1280000000
2019-08-17T00:06:00Z 20.0100000000
2019-08-17T00:06:00Z 8.2320000000
2019-08-17T00:12:00Z 19.7740000000
2019-08-17T00:12:00Z 8.0560000000

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

{{% note %}} Note: InfluxDB follows the standard order of operations. See InfluxQL mathematical operators for more on supported operators. {{% /note %}}

{{% /expand %}}

{{% expand "Select all data from more than one measurement" %}}

SELECT * FROM "h2o_feet","h2o_pH"

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description location pH water_level
2019-08-17T00:00:00Z below 3 feet santa_monica 2.0640000000
2019-08-17T00:00:00Z between 6 and 9 feet coyote_creek 8.1200000000
2019-08-17T00:06:00Z below 3 feet santa_monica 2.1160000000
2019-08-17T00:06:00Z between 6 and 9 feet coyote_creek 8.0050000000
2019-08-17T00:12:00Z below 3 feet santa_monica 2.0280000000
2019-08-17T00:12:00Z between 6 and 9 feet coyote_creek 7.8870000000
2019-08-17T00:18:00Z below 3 feet santa_monica 2.1260000000
2019-08-17T00:18:00Z between 6 and 9 feet coyote_creek 7.7620000000

{{% influxql/table-meta %}} Name: h2o_pH {{% /influxql/table-meta %}}

time level description location pH water_level
2019-08-17T00:00:00Z coyote_creek 7.00
2019-08-17T00:06:00Z coyote_creek 8.00
2019-08-17T00:06:00Z santa_monica 6.00
2019-08-17T00:12:00Z coyote_creek 8.00

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

{{% /expand %}}

{{% expand "Select all data from a measurement in a particular database" %}}

SELECT * FROM noaa.."h2o_feet"

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time level description location water_level
2019-08-17T00:00:00Z below 3 feet santa_monica 2.0640000000
2019-08-17T00:00:00Z between 6 and 9 feet coyote_creek 8.1200000000
2019-08-17T00:06:00Z below 3 feet santa_monica 2.1160000000
2019- 08-17T00:06:00Z between 6 and 9 feet coyote_creek 8.0050000000
2019-08-17T00:12:00Z below 3 feet santa_monica 2.0280000000
2019-08-17T00:12:00Z between 6 and 9 feet coyote_creek 7.8870000000

The query selects data from the h2o_feet measurement in the noaa database. The .. indicates the DEFAULT retention policy for the specified database.

{{% /expand %}}

{{< /expand-wrapper >}}

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: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time water_level location
2019-08-17T00:00:00Z 8.1200000000 coyote_creek
2019-08-17T00:00:00Z 2.0640000000 santa_monica
2019-08-17T 00:06:00Z 8.0050000000 coyote_creek
2019-08-17T00:06:00Z 2.1160000000 santa_monica
2019-08-17T00:12:00Z 7.8870000000 coyote_creek
2019-08-17T00:12:00Z 2.0280000000 santa_monica
2019-08-17T00:18:00Z 7.7620000000 coyote_creek
2019-08-17T00:18:00Z 2.1260000000 santa_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 %}} 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. {{% /note %}}

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: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time water_level
2019-08-17T00:00:00Z 8.1200000000
2019-08-17T00:00:00Z 2.0640000000
2019-08-17T00:06:00Z 8.0050000000
2019-08-17T00:06:00Z 2.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

{{< expand-wrapper >}}

{{% expand "Cast float field values to integers" %}}

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

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time water_level
2019-08-17T00:00:00Z 8.0000000000
2019-08-17T00:00:00Z 2.0000000000
2019-08-17T00:06:00Z 8.0000000000
2019-08-17T00:06:00Z 2.0000000000

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

{{% /expand %}}

{{% expand "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.

{{% /expand %}}

{{< /expand-wrapper >}}

Merge behavior

InfluxQL merges series automatically.

Example

{{< expand-wrapper >}}

{{% expand "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: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 4.4419314021

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: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 5.3591424203

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: {{% influxql/table-meta %}} name: h2o_feet
tags: location=coyote_creek {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 5.3591424203

{{% influxql/table-meta %}} name: h2o_feet
tags: location=santa_monica {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 3.5307120942

{{% /expand %}}

{{< /expand-wrapper >}}

Multiple statements

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

Examples

{{< tabs-wrapper >}} {{% tabs %}} InfluxQL shell InfluxDB API {{% /tabs %}}

{{% tab-content %}}

In the InfluxQL shell:

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

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 4.4419314021

{{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

time water_level
2019-08-17T00:00:00Z 8.12
2015-08-18T00:00:00Z 2.064

{{% /tab-content %}}

{{% tab-content %}}

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

{{% /tab-content %}} {{< /tabs-wrapper >}}