SELECT

Syntax

Parameters

• selectExpression

  This can be a column, a column inside a STRUCT, or an expression

• label

  Label to use for the projection expression. The label will be the only way to access this expression at higher scopes

Examples

To start, suppose we have the following table:

CREATE TYPE AS STRUCT restaurant_review (reviewer STRING, rating INT64);
CREATE TABLE restaurant (
    rest_no INT64,
    name STRING,
    reviews restaurant_review ARRAY,
    PRIMARY KEY(rest_no));

and the following records:

{"REST_NO": 42, "name": "Restaurant1", "reviews":
    [{"reviewer": "Reviewer11", "rating": 2},
    {"reviewer": "Reviewer22", "rating": 5},
    {"reviewer": "Reviewer21", "rating": 1}]}
{"REST_NO": 43, "name": "Restaurant2", "reviews":
    [{"reviewer": "Reviewer19", "rating": 1}]}
{"REST_NO": 44, "name": "Restaurant3", "reviews":
    [{"reviewer": "Reviewer41", "rating": 3},
    {"reviewer": "Reviewer55", "rating": 5}]}
{"REST_NO": 45, "name": "Restaurant4", "reviews":
    [{"reviewer": "Reviewer14", "rating": 3},
    {"reviewer": "Reviewer55", "rating": 2}]}

Projecting all columns

It is possible to query a table using normal select with optional predicates. For example:

SELECT * FROM restaurant;

rest_no	name	reviews
42	"Restaurant1"	[{"reviewer": "Reviewer11","rating": "2"}, {"reviewer": "Reviewer22","rating": "5"}, {"reviewer": "Reviewer21","rating": "1"}]
43	"Restaurant2"	[{"reviewer": "Reviewer19","rating": "1"}]
44	"Restaurant3"	[{"reviewer": "Reviewer41","rating": "3"}, {"reviewer": "Reviewer55","rating": "5"}]
45	"Restaurant4"	[{"reviewer": "Reviewer14","rating": "3"}, {"reviewer": "Reviewer55","rating": "2"}]

Note how * resolves all the attributes (top-level fields) of table restaurant and returns them as individual columns in the result set.

Projecting one column

It is also possible to project individual columns from a table, for example, suppose we want to only project restaurant names:

select name from restaurant

name
"Restaurant1"
"Restaurant2"
"Restaurant3"
"Restaurant4"

Projecting the entire row as a struct

It is possible to project the entire row as a single STRUCT column rather than expanding individual fields. There are two equivalent ways to do this.

Using (*)

SELECT (*) FROM restaurant;

This returns one column per row, named after the table, whose value is a STRUCT containing all of the table’s fields:

restaurant
{"REST_NO": 42, "NAME": "Restaurant1", "REVIEWS": [...]}
{"REST_NO": 43, "NAME": "Restaurant2", "REVIEWS": [...]}
{"REST_NO": 44, "NAME": "Restaurant3", "REVIEWS": [...]}
{"REST_NO": 45, "NAME": "Restaurant4", "REVIEWS": [...]}

Using the table name (or alias) as the projection expression

As a shorthand, using the table name directly in the SELECT list is equivalent to SELECT (*):

SELECT restaurant FROM restaurant;

If the table has an alias, use the alias:

SELECT r FROM restaurant r;

In both cases the result is identical to SELECT (*) FROM restaurant.

Note

If a column in the table has the same name as the table itself, the column takes priority and its value is returned instead of the row struct.

Structs can be nested further by wrapping (*) in another level of parentheses:

SELECT ((*)) FROM restaurant;

This returns a single column whose value is a STRUCT containing one field, which is itself the row STRUCT.

Projecting column inside nested a nested STRUCT

It is also possible to project a nested field provided non of its parents is repeated:

CREATE TYPE AS STRUCT A ( b B );
CREATE TYPE AS STRUCT B ( c C );
CREATE TYPE AS STRUCT C ( d D );
CREATE TYPE AS STRUCT D ( e E );
CREATE TYPE AS STRUCT E ( f int64 );
CREATE TABLE tbl1 (id int64, c C, a A, PRIMARY KEY(id));
SELECT a.b.c.d.e.f FROM tbl1;