CODEWITHSUNDEEP
sqlgoogle-bigquery
Tony
Tony

Reputation: 115

Unnest string array and transpose in Big query

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:

enter image description here

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

enter image description here

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.

Upvotes: 0

Views: 1342

Answers (1)

Aleix CC
Aleix CC

Reputation: 2089

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.

Upvotes: 2

Related Questions