BigQuery Insert into Nested records

Question

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

schema defination

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.

score 5 · Accepted Answer · answered Oct 19 '20 at 11:27

5

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';

answered Oct 19 '20 at 11:27

Gordon Linoff

1,242,037
58
646
786

It didn't work. I am getting following error `Query column 2 has type ARRAY> which cannot be inserted into column address, which has type STRUCT at [95:5]:` – Dinesh Oct 19 '20 at 11:59
My understanding is that we are inserting an array of Structs but BigQuery seems to think differently. In both of the above cases, BigQuery gives an error, however,nested records in select statement is exactly what I am after. – Dinesh Oct 19 '20 at 12:03
@Dinesh. . . I missed that. You need to make the record `REPEATABLE`. Right now, it does not contain an array. – Gordon Linoff Oct 19 '20 at 12:16
Thanks a lot @Gordon Linoff , that soved the issue – Dinesh Oct 19 '20 at 12:30

score 2 · Answer 2 · answered Oct 19 '20 at 12:15

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'
      )

answered Oct 19 '20 at 12:15

Sergey Geron

9,098
2
22
29

score 0 · Answer 3 · edited Mar 28 '22 at 06:51

0

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

edited Mar 28 '22 at 06:51

Elikill58

4,050
24
23
45

answered Mar 28 '22 at 05:25

vinay lokwani

1

If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/31391216) – miltonb Mar 31 '22 at 02:48

BigQuery Insert into Nested records

3 Answers3