Selectors
The SELECT statement is used to query data from a table in CQL.
The results are returned as a set of rows.
Selectors are used to specify which columns are queried and returned.
The SELECT statement can also transform the data on return using functions.
Table data also has related metadata that can be returned using metadata functions.
Return all columns
To return all columns from a table, use the wildcard * selector.
SELECT * FROM cycling.cyclist_name;Results
Since the * selector returns all columns, it is not necessary to specify the column names.
The more columns a table has, the more resources are required to return the data.
Return specific columns
Because the number of columns returned can affect performance, it is recommended to specify the columns to return, if possible.
For example, to return only the lastname column from the cycling.cyclist_name table:
SELECT lastname FROM cycling.cyclist_name;Results
To return multiple columns, separate the column names with a comma:
SELECT lastname, firstname FROM cycling.cyclist_name;Results
Columns can be renamed on return using an alias:
SELECT id, cyclist_age AS age FROM cycling.cyclist_alt_stats LIMIT 3;Results
|
A column alias cannot be used in the |
Return transformed data
The SELECT statement can transform the data on return using functions.
The functions are varied and can be used to manipulate the data in different ways.
For example, JSON can be used to return all columns as JSON, toJson to return selected column as JSON, or CAST can be used to change the data type of a column.
Distinct column values can be returned using the DISTINCT function.
The number of rows with a particular column can be returned using the COUNT function.
Any native or user-defined functions can be used to transform the data, such as the SUM or AVG function.
Any user-defined aggregate function can be used to transform the data, such as the average function.
The TOKEN function can be used to return the token value of a column.
To output selected data from a table in JSON format, use the JSON function:
SELECT JSON * FROM cycling.birthday_list;Results
Note the nested JSON structure in the output, with the column name as the key and the column value as the value.
To specify the JSON format for a particular column, enclose its name in the toJson() function:
SELECT id, commenter, toJson(created_at) FROM cycling.comments_vs;Results
id | commenter | system.tojson(created_at)
--------------------------------------+-----------+----------------------------
e8ae5cf3-d358-4d99-b900-85902fda9bb0 | John | "2017-04-01 14:33:02.160Z"
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | Alex | "2017-04-01 14:33:02.160Z"
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | Alex | "2017-03-21 21:11:09.999Z"
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | Alex | "2017-02-14 20:43:20.000Z"
c7fceba0-c141-4207-9494-a29f9809de6f | Amy | "2024-07-12 19:25:03.179Z"
c7fceba0-c141-4207-9494-a29f9809de6f | Amy | "2017-04-01 13:43:08.030Z"
c7fceba0-c141-4207-9494-a29f9809de6f | Amy | "2017-03-22 01:16:59.001Z"
c7fceba0-c141-4207-9494-a29f9809de6f | Amy | "2017-02-17 08:43:20.234Z"
c7fceba0-c141-3207-9494-a29f9809de6f | Amy | "2017-02-17 08:43:20.234Z"
c7fceba0-c141-7207-9494-a29f9809de6f | Amy | "2024-07-12 19:25:03.183Z"
e8ae5df3-d358-4d99-b900-85902fda9bb0 | Jane | "2017-04-01 14:33:02.160Z"
c7fceba0-c141-4207-9594-a29f9809de6f | Jane | "2017-02-17 08:43:20.234Z"
(12 rows)Note that the JSON-formatted timestamp column can include complete time zone information.
To cast a column to a different data type, use the CAST function:
SELECT CAST(created_at AS date) FROM cycling.comments;Results
If a table has duplicate values in a column, use the DISTINCT function to return only the unique values:
SELECT DISTINCT country FROM cycling.country_flag;Results
You may want to count the number of rows in a column.
Use the COUNT function to return the count:
SELECT start_month, MAX(start_day), COUNT(*) FROM cycling.events
WHERE year = 2017 AND discipline = 'Cyclo-cross';Results
To use the COUNT function with a WHERE clause, include the COUNT function in the SELECT list and the WHERE clause in the query.
For example, to get the number of cyclists from Belgium:
SELECT count(cyclist_name) FROM cycling.country_flag
WHERE country='Belgium';Results
A number of native functions can be used to transform the data.
The data is transformed only on return, rather than stored in the table.
These are general functions that are familiar to most people, such as the average, or AVG function:
SELECT AVG(race_points) FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;Results
User-defined functions can also be used to transform the data. For examples, the 'left` function can be used to return a column that is left-justified:
SELECT left(firstname, 1), lastname FROM cycling.cyclist_name;Results
User-defined aggregates can also be used to transform the data.
For examples, the 'average` function can be used to retrieve the average of the column cyclist_time_sec from a table:
SELECT average(cyclist_time_sec) AS Average FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
The TOKEN function can be used to return the token value of a column.
The token value is the hashed value of the partition key, thus it can only be used on the partition key column or columns.
This function is useful when using the token value in a query to filter data with a WHERE clause.
To return the token value of the race_id column in the cycling.race table:
SELECT race_name AS name,race_year AS year FROM cycling.rank_by_year_and_name
WHERE TOKEN(race_year,race_name) >= 4582455970709790046;Results
Return column metadata
Each non-primary key column in a table has metadata associated with it.
The WRITETIME function returns the timestamp of the last write to a column.
The TTL function returns the time to live of a column.
It is important to note that the metadata functions are not available for all columns.
For example, the metadata functions are not available for columns with a data type of counter or primary key columns.
Additionally, the metadata functions are not available for columns that are part of a collection or a user-defined type that is not frozen.
To return the writetime of the firstname column in the cycling.cyclist_points table:
SELECT WRITETIME (firstname) FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;Results
The TTL function returns the time to live of a column.
This function is useful when using Time to Live (TTL) to expire data in a table.
If a TTL is set on a column, the data is automatically deleted after the specified time has elapsed.
To return the TTL of the race_points column in the cycling.cyclist_points table:
SELECT TTL(race_name) FROM cycling.calendar WHERE race_id = 200;Results
ttl(race_name)
----------------
198
(1 rows)