Unnest string array and transpose in Big query-CodePudding

I'm using Bigquery, I've a table A with string array and I need to cast to int64/string ( if possible ) so I can join with table B which of Int64/string

The main ask here is:

I've a table A, where I've string array mapped with Ref ID as below:

I'm trying to get unnest and my desired output should be as below.

I did tried below script:

SELECT a0_string_arrat,
ref_id
FROM TableA AS t,
  t.String_array AS a0_String_array

But the challenge with above script is, I've close to 1000 Ref IDs, but my output is resulting only 100

If I try the below, I'm able to get all 1000 rows.

SELECT string_array,
ref_id
FROM TableA

The end goal is to I need to unnest and cast to Int64/string. The above script is not working for my need. can someone help on this.

CodePudding user response：

You can use CROSS JOIN UNNEST() in order to get the values from the array attributed to each ref_id:

select
  ref_id,
  unnested_numbers
from tablea
cross join unnest(string_array) as unnested_numbers
order by 2, 1

This should give you the desired output that you specified.