CODEWITHSUNDEEP
sqlgoogle-bigquery
Purit Kittipakorn
Purit Kittipakorn

Reputation: 3

Bigquery Column name is ambiguous

in my table I have a column name "Status" and a RECORD type column name "Payment", which has another column name "Status" inside it. So I basically have 2 columns: "Status" and "Payment.Status". When I want to UNNEST the STRUCT data("Payment" column) and use the data, I will get the error message Column name STATUS is ambiguous

How do I solve this problem? Thanks in advance

Upvotes: 0

Views: 2682

Answers (1)

AYR
AYR

Reputation: 1179

When you are trying to use 'STATUS', BigQuery does not know to which column you are referring to. You can overcome this with giving your UNNEST() an alias and using it to reference the specific column you are using.

Example:

with example_data as (
    select true as status,
    [STRUCT( 123 as id, false as status)] as payment
)

select e.status, 
       p.id,
       p.status as payment_status
from example_data e, unnest(payment) as p

Upvotes: 1

Related Questions