How can I rename the column in BigQuery table?

Question

I have the following structure:

|       Field name                   |    Type    |    Mode    |
--------------------------------------------------------------------
event_id                             |   STRING   |  NULLABLE  |
payload                              |   RECORD   |  NULLABLE  |
  |-version                          |   STRING   |  NULLABLE  |
  |-plan                             |   RECORD   |  NULLABLE  |
      |-legs                         |   RECORD   |  REPEATED  |
          |-id                       |   STRING   |  NULLABLE  |
          |-shipmentLinks            |   RECORD   |  REPEATED  |
              |-id                   |   STRING   |  NULLABLE  |
              |-source               |   STRING   |  NULLABLE  | 

And I want to rename the column id to shipmentLinkId in a new table by running this query:

CREATE TABLE `project.dataset.table_renamed`
AS
SELECT 
  event_id, 
  payload.version,
  payload.plan.legs.id,
  payload.plan.legs.shipmentLinks.id AS shipmentLinkId,
  payload.plan.legs.shipmentLinks.source
FROM `project.dataset.table_name`

But I can not run the query successfully. The error I get:

Cannot access field id on a value with type ARRAY<STRUCT<id STRING, shipmentLinks STRUCT<id STRING, source STRING>>> at

How can I fix that?

Thanks in advance,

Answer 1

I want to change the schema of original table with another field name.

Below will return the table with field name shipmentLinkId instead of id.

CREATE TABLE `project.dataset.table_renamed` AS
SELECT event_id,
       (SELECT AS STRUCT payload.version,
         ARRAY(
           SELECT AS STRUCT
                  id,
                  ARRAY( -- change *id* to *shipmentLinkId*
                    SELECT AS STRUCT id AS shipmentLinkId, source FROM legs.shipmentLinks
                  ) shipmentLinks
             FROM t.payload.plan.legs
         ) AS plan
       ) AS payload
  FROM `project.dataset.table_name` t;

Query results

• you can see id is changed to shipmentLinkId