Change in select query response for GoogleSQL Vs Legacy SQL

[appflowpersiste]

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.

[ms4446]

Yes, you can achieve the same response as with the legacy SQL dialect using Standard SQL by utilizing the UNNEST function strategically.

In your example, the current Standard SQL query unnests all arrays, resulting in a row for each element within each array. This causes the number of rows to increase compared to the legacy SQL response.

Here's how to achieve the desired 3-row response using Standard SQL:

SELECT
  address_array,
  address_struct.*,
  ARRAY(SELECT * FROM UNNEST(address_array_of_struct) AS el) AS address_array_of_struct,
  address_struct_in_struct.*,
  ARRAY(
    SELECT * FROM UNNEST(address_array_of_nested_structs) AS el) AS address_array_of_nested_structs
FROM test.array_struct_tbl;

This query:

• Uses UNNEST only within subqueries to prevent individual elements from creating separate rows.
• Uses ARRAY(SELECT * FROM ...) to convert the results of subqueries back into arrays as expected.

This approach maintains the original structure of the arrays while also unnesting their elements within the respective columns. As a result, you'll get the same 3-row response as with the legacy SQL query.

[appflowpersiste]

Hi,  thank you for the suggestion.

The above  giving the following error -

"ARRAY subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [1:41]"

Can you help me with this error

[ms4446]

The error message you're encountering indicates that when using an ARRAY subquery to unnest and then re-aggregate the elements into an array, if you're selecting more than one column, you must use SELECT AS STRUCT to construct the elements of the array as STRUCT values.

Corrected Query with SELECT AS STRUCT for Arrays of Structs:

SELECT
  address_array,
  address_struct.col1 AS address_struct_col1,
  address_struct.col2 AS address_struct_col2,
  ARRAY(
    SELECT AS STRUCT col1, col2
    FROM UNNEST(address_array_of_struct)
  ) AS address_array_of_struct,
  address_struct_in_struct.col1.col1_1 AS address_struct_in_struct_col1_1,
  address_struct_in_struct.col1.col1_2 AS address_struct_in_struct_col1_2,
  address_struct_in_struct.col2 AS address_struct_in_struct_col2,
  ARRAY(
    SELECT AS STRUCT
      col1.col1_1,
      col1.col1_2,
      col2
    FROM UNNEST(address_array_of_nested_structs)
  ) AS address_array_of_nested_structs
FROM
  test.array_struct_tbl;

[appflowpersiste]

Thank you for your reply.

Yes, I tried this but now the response gives me only 1 row, instead of 3 , its same as using select * , but maintain backward compatibility I need the response as the Legacy SQL one. 

[ms4446]

Here's a query that should give you the same result as Legacy SQL by using a combination of LEFT JOIN and ARRAY aggregation:

SELECT
 address_array,
 address_struct.col1 AS address_struct_col1,
 address_struct.col2 AS address_struct_col2,
 ARRAY(
  SELECT AS STRUCT col1, col2
  FROM UNNEST(t.address_array_of_struct)
 ) AS address_array_of_struct,
 address_struct_in_struct.col1.col1_1 AS address_struct_in_struct_col1_1,
 address_struct_in_struct.col1.col1_2 AS address_struct_in_struct_col1_2,
 address_struct_in_struct.col2 AS address_struct_in_struct_col2,
 ARRAY(
  SELECT AS STRUCT col1.col1_1, col1.col1_2, col2
  FROM UNNEST(t.address_array_of_nested_structs)
 ) AS address_array_of_nested_structs
FROM
 test.array_struct_tbl t;

This query should return the same number of rows as the Legacy SQL query. It selects the address_array and address_struct fields directly, and for the address_array_of_struct and address_array_of_nested_structs fields, it uses a subquery with UNNEST and then re-aggregates the results back into an array. This approach should maintain the row structure without multiplying the rows.

[appflowpersiste]

Hi, Actually this is still giving the original response of 1 row,  I guess because of the 

ARRAY(
  SELECT AS STRUCT)

[ms4446]

Give this a try:

SELECT
ARRAY(
SELECT AS STRUCT address_array, offset
FROM UNNEST(address_array) AS address_array WITH OFFSET AS offset
) AS address_array_with_offset,
address_struct.col1 AS address_struct_col1,
address_struct.col2 AS address_struct_col2,
ARRAY(
SELECT AS STRUCT col1, col2, offset
FROM UNNEST(address_array_of_struct) WITH OFFSET AS offset
) AS address_array_of_struct_with_offset,
address_struct_in_struct.col1.col1_1 AS address_struct_in_struct_col1_1,
address_struct_in_struct.col1.col1_2 AS address_struct_in_struct_col1_2,
address_struct_in_struct.col2 AS address_struct_in_struct_col2,
ARRAY(
SELECT AS STRUCT col1.col1_1, col1.col1_2, col2, offset
FROM UNNEST(address_array_of_nested_structs) WITH OFFSET AS offset
) AS address_array_of_nested_structs_with_offset
FROM
test.array_struct_tbl

[appflowpersiste]

Actually, It is still giving the same response with one data.

[ms4446]

Here's a revised approach that uses a CROSS JOIN to flatten the arrays, but only when there are elements present, and a LEFT JOIN to ensure that rows without array elements are still included:

SELECT
address_array,
address_struct.col1 AS address_struct_col1,
address_struct.col2 AS address_struct_col2,
address_array_of_struct.col1 AS address_array_of_struct_col1,
address_array_of_struct.col2 AS address_array_of_struct_col2,
address_struct_in_struct.col1.col1_1 AS address_struct_in_struct_col1_1,
address_struct_in_struct.col1.col1_2 AS address_struct_in_struct_col1_2,
address_struct_in_struct.col2 AS address_struct_in_struct_col2,
address_array_of_nested_structs.col1.col1_1 AS address_array_of_nested_structs_col1_1,
address_array_of_nested_structs.col1.col1_2 AS address_array_of_nested_structs_col1_2,
address_array_of_nested_structs.col2 AS address_array_of_nested_structs_col2
FROM test.array_struct_tbl
LEFT JOIN UNNEST(test.array_struct_tbl.address_array_of_struct) AS address_array_of_struct
LEFT JOIN UNNEST(test.array_struct_tbl.address_array_of_nested_structs) AS address_array_of_nested_structs