Dbt table materializations with nested repeated fields

Hi,

I am trying to use the Table Materialization to create a Bigquery table using the query with nested struct.

Model File:
{{ config(materialized=‘table’) }}

SELECT
customer_id,
ARRAY_AGG(STRUCT(first_name,middle_name
,[STRUCT(MOBILE_PHONE_NUMBER)]
))
as cutomer_details
FROM customer
GROUP BY
customer_id

It fails with below error:

Completed with 1 error and 0 warnings:
Database Error in model tnf_test (models/tnf_test.sql)
Illegal field name:

It works fine the moment I comment out the array of struct i.e. [STRUCT(MOBILE_PHONE_NUMBER)]

Can anyone help, how can I use nested struct ?

Hey @amal.kmr.singh ,
Can you please detail the table schema for you customer table and describe the granularity?
At first it seems that the square brackets are extra there and you can take them off.
maybe try this:

{{ config(materialized=‘table’) }}

SELECT
customer_id,
ARRAY_AGG(STRUCT(first_name,middle_name
,MOBILE_PHONE_NUMBER) as cutomer_details)
FROM  `customer`
GROUP BY
customer_id

But for you to achieve what you want I’d say a little more detail on your part would be helpful!
Thanks and happy dbt’ing

I faced a similar issue in Dataform. It seems that Bigquery does not like nested columns without specifying the exact struct type:

– throws the illegal field error
create table sandbox.tst01 as
select struct(1,‘x’) col1;

– executes successfully
create table sandbox_maugr1.tst01 as
select struct<a int64, b string>(1,‘x’) col1;