CODEWITHSUNDEEP
uniongoogle-bigquery
Lior
Lior

Reputation: 1467

BigQuery union over nested fields

I'm trying to create a union of 3 tables. All 3 tables are subselects on the same table: each subselect contains only one field, with the same alias to the field on all subselects, so the resulting schema will be compatible and the union will succeed (following the example from Support UNION function in BigQuery SQL).

The resulting query yields an error:

Union results in ambiguous schema. [foo] is ambiguous and is aliasing multiple fields. Aliased fields: ...

It may be that there error is related to the fact the field I select is a nested in multiple records and repeated fields.

Fictitious Query example:

select * from
(SELECT record.list1.list2.listA.foo as foo from sample),
(SELECT record.list1.list2.listB.foo as foo from sample),
(SELECT record.list1.list2.listC.foo as foo from sample)

See job job_eZm0F1cGA2leE37D8-N5NHNTTYU for a concrete example (this is a table that contains data I cannot share).

Upvotes: 3

Views: 2167

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

You can reproduce this with a public dataset:

SELECT x FROM
  (SELECT phoneNumber.areaCode x
   FROM [bigquery-samples:nested.persons_living] LIMIT 1),
  (SELECT citiesLived.numberOfYears x
   FROM [bigquery-samples:nested.persons_living] LIMIT 1);

Error: Union results in ambiguous schema.
       [x] is ambiguous and is aliasing multiple fields.
       Aliased fields: x,CitiesLived.x,

As noted, this only happens when mixing different multiple records and repeated fields - and that has a quick fix: FLATTEN() before querying:

SELECT x FROM
  (SELECT phoneNumber.areaCode x
   FROM [bigquery-samples:nested.persons_living] LIMIT 1),
  (SELECT citiesLived.numberOfYears x
   FROM (FLATTEN([bigquery-samples:nested.persons_living], citiesLived)) LIMIT 1);

Upvotes: 2

Related Questions