CODEWITHSUNDEEP
google-bigquery
Louis
Louis

Reputation: 99

Question on implicit UNNEST and the effects of aliasing, or not, the unnested array field

I'm trying to understand the finer points of implicit UNNEST. Most especially, how the unnested field is referred to if you don't alias it.

EXAMPLE 1

WITH
  arry AS (
  SELECT
    ['foo','bar'] AS f1
  UNION ALL
  SELECT
    ['a', 'b'] AS f1)

SELECT
  f1
FROM
  arry
CROSS JOIN
  arry.f1

returns as expected:

ROW         f1
1           foo
2           bar
3           a
4           b

But if you alias arry.f1 as such:

EXAMPLE 2

WITH
  arry AS (
  SELECT
    ['foo','bar'] AS f1
  UNION ALL
  SELECT
    ['a', 'b'] AS f1)

SELECT
  f1
FROM
  arry
CROSS JOIN
  arry.f1 AS alias

you get back:

ROW         f1
1           foo
            bar
2           foo
            bar
3           a
            b
4           a
            b

Trying the following shed some light on what is happening:

EXAMPLE 3

WITH
  arry AS (
  SELECT
    ['foo','bar'] AS f1
  UNION ALL
  SELECT
    ['a', 'b'] AS f1)

SELECT
  f1,  
  arry.f1 as arry_f1,
  alias
FROM
  arry
INNER JOIN
  arry.f1 AS alias

You get back:

ROW    f1     arry_f1   alias
1      foo    foo       foo
       bar    bar
2      foo    foo       bar
       bar    bar
3      a      a         a
       b      b
4      a      a         b
       b      b

So obviously, the f1 in my first 2 select clauses do not refer to the same thing. In:

SELECT
  f1
FROM
  arry
INNER JOIN
  arry.f1

f1 in the SELECT is referring to the implicitely unnested arry.f1.

In the second case where I alias, 

SELECT
  f1
FROM
  arry
INNER JOIN
  arry.f1 AS alias

f1 refers to the array field f1 of the arry table whereas alias refers to the unnested alias.f1 field in the SELECT as the third example makes clear.

I don't understand why f1 can be assumed to be what it is in example 1:

SELECT
  f1
FROM
  arry
INNER JOIN
  arry.f1

Should't it be ambiguous as it could be referring to field f1 of arry (which is an array) or the implicitely unnested arry.f1?

It certainly threw me off when I saw the results. I see nowhere in the documentation where it says how the naming and scoping works with implicit unnesting.

Upvotes: 1

Views: 109

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

I don't understand why f1 can be assumed to be what it is in example 1:

My few cents ...

Even though, I am using that feature a lot and for quite a time to simplify/streamline code - I am not sure if it is a “designed feature” or just “feature by a chance”

So, when you use JOIN arry.f1 it is being treated as a table (sub-table of arry table) – I think because of pattern dataset.table here. As such each and every element in f1 array is treated as a value in separate row (with still JOIN is applied on parent row level)

Upvotes: 2

Related Questions