BigQuery UNNEST causing duplicate column names

Question

I am trying to create a view that includes unnesting some of the values in a table called customers in BigQuery. An example of one row of the customers table in JSON format looks like the following:

{
    "customer_id": "12345",
    "created_date": "2020-12-20",
    "customer_purchases": [
        {
            "created_date": "2020-12-20",
            "item_id": 100
        }
    ]
}

I want the view such that each row is a customer purchase with exactly the same columns as in the customers table. I therefore unnest the customer purchases like the following:

SELECT
  *
FROM `sales.customers`,
UNNEST (customer_purchases)

However I now get an error which is:

Duplicate column name 'created_date' in definition of view 'sales.customers_purchases_view'

I could create a monster query where I rename all the fields that are duplicates but this is not ideal and will be hard to maintain ongoing. Is there anyway the view could have a prefix or something for any columns that have been unnested?

please provide sample of input data and respective expected output - see How to create a Minimal, Reproducible Example — Mikhail Berlyant

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-12-20T13:26:17

I would typically write this as:

SELECT c.* except (customer_purchases), purchase
FROM `sales.customers` c UNNEST
     (customer_purchases) purchase;

This puts the purchases in the result set as a struct.

You can also remove and rename names:

If you want the individual columns, you can rename them:

SELECT c.* except (customer_purchases, created_date),
       c.created_date as customer_created_date,
       purchase.*
FROM `sales.customers` c UNNEST
     (customer_purchases) purchase;

Or:

SELECT c.* except (customer_purchases),
       purchase.* except (created_date)
       purchase.created_date as purchase_created_date
FROM `sales.customers` c UNNEST
     (customer_purchases) purchase;

BigQuery UNNEST causing duplicate column names

2 Answers