[SOLVED] Use the "as" keyword in nested fields in Bigquery

Use the "as" keyword in nested fields in Bigquery

i am querying BigQuery and some of my tables have records aka nested fields. Now, since i am using Dataform to perform some transformations, i want to rename some of those fields, for instance i want to rename person.details.firstName to person.details.first_name

But i cannot use the "as" keyword when specifying the target field-name and include the hierarchy in the sense i could do person.details.firstName as person_first_name but not person.details.firstName as person.details.first_name

To describe the problem in a more simple way, this:

person.details.firstName

should become this:

person.details.first_name

Is there a way to do this?

Thanks in advance Best regards

I tried to query it like this:

select person.details.firstName as person.details.first_name

But that did not work. In other threads i saw people using subquery, but that is not a very good solution for me, since in some tables i have many records with many subfields. Is there another way of doing it?

Solution

If you want to rename a nested field you will have to recreate the struct it is in with the new name:

WITH
  baseData AS (
  SELECT
    STRUCT(STRUCT("myName" AS firstName) AS details) AS person)

SELECT
   STRUCT(STRUCT(person.details.firstName AS first_name) AS details) AS person
FROM
   baseData