UNNEST array and assign to new columns with CASE WHEN

Question

I have following BigQuery table, which has nested structure, i.e. example below is one record in my table.

Id | Date | Time | Code

AQ5ME | 120520 | 0950 | 123

---------- | 150520 | 1530 | 456

My goal is to unnest the array to achieve the following structure (given that 123 is the Start Date code and 456 is End Date code):

Id | Start Date | Start Time | End Date | End Time

AQ5ME | 120520 | 0950 | 150520 | 1530

I tried basic UNNEST in BigQuery and my results are as follows:

Id | Start Date | Start Time | End Date | End Time

AQ5ME | 120520 | 0950 | NULL | NULL

AQ5ME | NULL | NULL | 150520 | 1530

Could you please support me how to unnest it in a correct way as described above?

You should just group by ID, and use an aggregation function for other columns such as any_value, min, max — Sabri Karagönen, May 12 '20 at 09:38

score 2 · Answer 1 · answered May 12 '20 at 09:42

2

You can calculate mins and max within the row, and extract them as a new column. Since you didn't show the full schema, I assume Date and Time are separate arrays. For that case, you can use that query:

SELECT Id, 
    (SELECT MIN(D) from UNNEST(Date) as d) as StartDate, 
    (SELECT MIN(t) from UNNEST(Time) as t) as StartTime, 
    (SELECT MAX(D) from UNNEST(Date) as d) as EndDate, 
    (SELECT MAX(t) from UNNEST(Time) as t) as EndTime
FROM table

answered May 12 '20 at 09:42

Sabri Karagönen

2,212
1
14
28

Thank you Sabri for your answer, it helped a lot and that's what I needed, much appreciated. Follow-up question - after setting up a view using your code all of the fields are treated as Repeated Integer and cannot be used in ORDER BY section. Do you know why this field is Repeated given that we used aggregation function and selected only one value out of all of the unnested ones? – markoo May 19 '20 at 11:53
Can you show your example query? If you add for ex: `ORDER BY StartDate, StartTime`, it should be working. – Sabri Karagönen May 19 '20 at 12:50
Sure, so on my example it would look similarly to case below: `SELECT Id, (SELECT MIN(D) from UNNEST(Date) as d) as StartDate FROM table ORDER BY StartDate DESC` My situation is more complex though. My origin field for StartDate was let's say ScheduledDate with three descendants: Time, Date and Index. As first step I did: `ARRAY(SELECT date FROM UNNEST(ScheduledDate))` And then I went on with using MIN and MAX from your answer. – markoo May 20 '20 at 11:58
I still would like to see the original sample data to give a better answer. – Sabri Karagönen May 22 '20 at 00:28

score 0 · Answer 2 · answered May 19 '20 at 12:00

As in Sabri's response - using aggregation functions while unnesting works perfectly. To use this fields later on for sorting purposes (in ORDER BY statement) SAFE_OFFSET[0] can be used, like for example below:

...
ORDER BY StartDate[SAFE_OFFSET(0)] ASC

UNNEST array and assign to new columns with CASE WHEN

2 Answers2