Convert struct values to row in big query

Question

I want to convert values of struct to independent row

My table looks like

|id | details
| 1 | {d_0:{id:'1_0'},d_1:{id:'1_1'}}
| 2 | {d_0:{id:'2_0'},d_1:{id:'2_1'}}

Expected Result (will be flattening the inner struct here)
| id  |
|'1_0'|
|'1_1'|
|'2_0'|
|'2_1'|

Since IDK how many fields will be there in details is there any way to convert all the individual fields of the struct as independent rows.

The schema for all values in the details.d_0, details.d_1,... will be the same.

Any help or pointer to resources is appreciated.

user_17720353 · Answer

You may use this query that iterates array to achieve your desired output:

Creating table:

CREATE TABLE `..` as 
WITH data AS (
 SELECT "1" AS id, STRUCT(STRUCT( '1_0' as id) as d_0, STRUCT( '1_1' as id) as d_1) as details,
 union all SELECT "2" AS id, STRUCT(STRUCT( '2_0' as id) as d_0, STRUCT( '2_1' as id) as d_1) as details
),

tier_1 as (
select id,details.*  from data 
)

select * from tier_1
Actual Query:
DECLARE i INT64 DEFAULT 0;

DECLARE query_ary ARRAY DEFAULT 

  ARRAY(
select concat(column_name,'.id')  from  `.INFORMATION_SCHEMA.COLUMNS`
    WHERE
      table_name =  AND regexp_contains(column_name, r'd\_\d')
  );
CREATE TEMP TABLE result(id STRING);
LOOP
  SET i = i + 1;
  IF i > ARRAY_LENGTH(query_ary) THEN 
    LEAVE; 
  END IF;
  EXECUTE IMMEDIATE '''
   INSERT result
   SELECT ''' || query_ary[ORDINAL(i)] || ''' FROM `..


`
  ''';

END LOOP; 

SELECT * FROM result;
Output:

user_5221944 · Answer

Consider below approach

select id from your_table,
unnest(split(translate(format('%t', details), '()', ''), ', ')) id

if applied to sample data in your question as

with your_table as (
 select "1" id, struct(struct('1_0' as id) as d_0, struct('1_1' as id) as d_1) details union all 
 select "2", struct(struct('2_0'), struct('2_1')) 
)

output is

Convert struct values to row in big query

2 Answers