BigQuery Insert into Nested records

Question

I have a table that has a nested record (please see attached snapshot).

I am trying to do a simple insert but it's not working.

INSERT  INTO `my_project.my_dataset.test_table`(name,address,comments)
values( 
'my_name' as name,
  [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
   STRUCT('PQR' as line1,'STU' as line2,20 as code)],
   'Comment') 

Any idea what's wrong with this SQL insert statement?

Thanks a lot for your help.

Answer 1

Don't assign names in values. Try:

INSERT  INTO `my_project.my_dataset.test_table`(name,address,comments)
    values('my_name',
           [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
            STRUCT('PQR' as line1,'STU' as line2,20 as code)],
           'Comment'
          ) 

Or use insert . . . select:

insert into `my_project.my_dataset.test_table`(name,address,comments)
    select 'my_name' as name,
           [STRUCT('ABC' as line1,'XYZ' as line2,10 as code),
            STRUCT('PQR' as line1,'STU' as line2,20 as code)],
           'Comment';

Answer 2

Consider changing address Mode to REPEATED:

Also as name needs to be removed:

INSERT  INTO `my_project.my_dataset.test_table`(name, address, comments)
values('my_name',
       [STRUCT('ABC','XYZ',10), STRUCT('PQR','STU',20)],
       'Comment'
      )

Answer 3

Is there any way that I can enter data in address.code using insert statement. eg:

Something like that:

insert into `my_project.my_dataset.test_table`(name,address.code) select myname,[STRUCT('ABC')] from tab1