Nesting a table, column of type string in BigQuery

I have a query that I am running against my dataset in BigQuery. The data is partly the google analytics data for a particular website. I want to be able to nest the string values for my key_web, to simplify the output and get the option to go deeper in detail if I want to. The prd.key_web is of type string. I have no idea how to do it ! It is a one to many relationship between the user who can have multiple rows in the table. All fields are of type string except for prd.dat_log.

A simpler query would be:

 select
        prd.key_web
        , dat_log AS date
        , prd.nrb_fp AS nrb_fp
        , prd.tps_fp AS tps_fp
        , prd.univ
        , prd.suniv
        , prd.fam
        , prd.sfam
    from product as prd

Answer

Simple ARRAY_AGG function can create nested array in aggregation

select
  prd.key_web,
  ARRAY_AGG(STRUCT(
    dat_log,
    prd.nrb_fp,
    prd.tps_fp,
    prd.univ,
    prd.suniv,
    prd.fam,
    prd.sfam)) AS details
from
  product as prd
group by
  key_web

Advertisement

Answer