Flatten multiple arrays with uneven lengths in BigQuery

Question

I'm trying to flatten arrays in different columns with different lengths without duplicating the results.

For example (using standard SQL):

WITH
  x AS (
  SELECT
    ARRAY[1,
    2,
    3] AS a,
    ARRAY[1,
    2] AS b)
SELECT
  a,
  b
FROM
  x,
  x.a,
  x.b

Produces:

+-----++-----+
| a   |   b  |
+-----++-----+
|   1 |   1  |
|   1 |   2  |
|   2 |   1  |
|   2 |   2  |
|   3 |   1  |
|   3 |   2  |
+-----++-----+

It should look like this:

+-----++-----+
| a   |   b  |
+-----++-----+
|   1 |   1  |
|   2 |   2  |
|   3 | null |
+-----++-----+

@RaymondNijland - cross join produces the same result. Could you provide an example to illustrate your point? — ethanenglish
– ethanenglish, Commented Jun 13, 2019 at 15:58
@RaymondNijland - The example is in the question. Please paste the SQL into BigQuery and hit run to reproduce the example in the question. — ethanenglish
– ethanenglish, Commented Jun 13, 2019 at 16:07

Gordon Linoff · Accepted Answer · 2019-06-13 16:20:36Z

2

You can use JOIN:

SELECT a, b
FROM x LEFT JOIN
     UNNEST(x.a) a left join
     unnest(x.b) b 
     ON a = b;

answered Jun 13, 2019 at 16:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ethanenglish Over a year ago

This works really well. How would you add more columns beyond "a" and "b"? Let's say you had 2 more nested columns. How would you do your join? Initially, an "and" statement made sense ("... a = b and b = c") but it just duplicated "a" and "b". Any thoughts?

Gordon Linoff Over a year ago

@ethanenglish . . . You can keep adding additional unnest()s. If you cannot get it to work, you should ask another question.

ethanenglish Over a year ago

I just did: stackoverflow.com/questions/56586399/… :)

Collectives™ on Stack Overflow

Flatten multiple arrays with uneven lengths in BigQuery

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related