sql-tests/inputs/struct.sql

0001 CREATE TEMPORARY VIEW tbl_x AS VALUES
0002   (1, NAMED_STRUCT('C', 'gamma', 'D', 'delta')),
0003   (2, NAMED_STRUCT('C', 'epsilon', 'D', 'eta')),
0004   (3, NAMED_STRUCT('C', 'theta', 'D', 'iota'))
0005   AS T(ID, ST);
0006
0007 -- Create a struct
0008 SELECT STRUCT('alpha', 'beta') ST;
0009
0010 -- Create a struct with aliases
0011 SELECT STRUCT('alpha' AS A, 'beta' AS B) ST;
0012
0013 -- Star expansion in a struct.
0014 SELECT ID, STRUCT(ST.*) NST FROM tbl_x;
0015
0016 -- Append a column to a struct
0017 SELECT ID, STRUCT(ST.*,CAST(ID AS STRING) AS E) NST FROM tbl_x;
0018
0019 -- Prepend a column to a struct
0020 SELECT ID, STRUCT(CAST(ID AS STRING) AS AA, ST.*) NST FROM tbl_x;
0021
0022 -- Select a column from a struct
0023 SELECT ID, STRUCT(ST.*).C NST FROM tbl_x;
0024 SELECT ID, STRUCT(ST.C, ST.D).D NST FROM tbl_x;
0025
0026 -- Select an alias from a struct
0027 SELECT ID, STRUCT(ST.C as STC, ST.D as STD).STD FROM tbl_x;