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