BigQuery - Get fields of nested Repeated Records

Question

I am working with BigQuery tables that can have many levels of nested repeated record fields, as shown in the example. I need to make a select on the given table, extract only some fields and ignore others, and at the end have the same structure except for the ignored fields. I think I have to work with array_agg and unnest to get only the required fields from the repeated record fields, but don't know how to do. In the example, I want to keep only DatiRighe and DatiRigheDettaglio as Structs and for each of them I want to keep everything except DatiRighe.Nota and DatiRigheDettaglio.cod_iva.

Answer 1

Try the following query for your requirement:

SELECT
 * REPLACE((
   SELECT
     AS STRUCT * EXCEPT(Nota)
   FROM (
     SELECT
       AS STRUCT DatiRighe.* REPLACE((
         SELECT
           AS STRUCT DatiRighe.DatiRigheDettaglio.* EXCEPT(cod_iva))AS DatiRigheDettaglio) )) AS DatiRighe)
FROM
 `my-project_id.database.table`

In the query, I used EXCEPT to remove the unwanted column and I used REPLACE to replace the corresponding structure with a new modified one. Let me know if it helps.