Hi,
When hitting ".../queries" API to fetch data for record datatype with repeated mode I found the response structure has changed, data is now coming in nested form.
Is there a way how I can write a select query so to get the same response as with the legacy sql dialect so to preserve a backward compatibility.
I have tried to unnest the response but am getting more number of rows then the one while querying using legacy dialect.
I am using the following Table:
create table test.array_struct_tbl
(
address_array ARRAY<INT64>,
address_struct STRUCT<col1 STRING, col2 INT64>,
address_array_of_struct ARRAY<STRUCT<col1 STRING, col2 INT64>>,
address_struct_in_struct STRUCT<col1 STRUCT<col1_1 STRING, col1_2 INT64>, col2 STRING>,
address_array_of_nested_structs ARRAY<STRUCT<col1 STRUCT<col1_1 STRING, col1_2 INT64>, col2 STRING>>
);
INSERT INTO testing.array_struct_tbl (
address_array,
address_struct,
address_array_of_struct,
address_struct_in_struct,
address_array_of_nested_structs
)
VALUES (
[1, 2, 3],
STRUCT('Value1', 42),
[STRUCT('Value2', 55), STRUCT('Value3', 67)],
STRUCT(STRUCT('Nested1', 123), 'Nested2'),
[
STRUCT(STRUCT('Nested3', 789), 'Nested4'),
STRUCT(STRUCT('Nested5', 987), 'Nested6')
]
);
Using the legacy sql query :
select * from Array_struct.array_struct_tbl
Response : Give me 3 rows
Using the Standard SQL query :
select address_array, address_struct.*, address_array_of_struct.*, address_struct_in_struct.col1.col1_1,address_struct_in_struct.col1.col1_2,address_struct_in_struct.col2,address_array_of_nested_structs.col1.col1_1,address_array_of_nested_structs.col1.col1_2,address_array_of_nested_structs.col2 from Array_struct.array_struct_tbl, unnest(address_array) as address_array, unnest(address_array_of_struct) as address_array_of_struct, unnest(address_array_of_nested_structs) as address_array_of_nested_structs
Response : Give me 12 rows
Can someone guide if there is a way to get the 3 records only using StandardSQL and if yes then what needs to be changed in the query.
LinkedIn
Twitter
