google-bigquerybigquery-udf

Insert several data in BIGQUERY in a column of type RECORD


I have a table in bigquery with the following structure:

CREATE TABLE MY_TABLE (
  name STRING,
  values STRUCT<model STRING, group BOOL>
)

What I want is to insert several data in the values column related to the same name.

This is what I'm doing:

INSERT INTO MY_TABLE  (name ,values)
VALUES (
  'export',
  STRUCT('model1', false)
),
(
  'export',
  STRUCT('model2', true)
)

This is what I get:

name values.model values.group
export model1 false
export model2 true

This is what I want:

name values.model values.group
export model1 false
model2 true

How can I insert several data for in the RECORS type column for the name column without having to repeat all the data? I need a record for each name, but that contains several values. I don't know if this is the correct way to create the table to achieve this.


Solution

  • You might consider below.

    CREATE TEMP TABLE MY_TABLE (
      name STRING,
      values ARRAY<STRUCT<model STRING, `group` BOOL>>
    );
    
    INSERT INTO MY_TABLE (name ,values)
    VALUES ( 'export', [STRUCT('model1', false), STRUCT('model2', true)] ),
           ( 'import', [STRUCT('model3', true), STRUCT('model4', false)] )
    ;
    
    SELECT * FROM MY_TABLE;
    

    Query results

    enter image description here

    or,

    CREATE TEMP TABLE MY_TABLE AS
    SELECT 'export' AS name, 
           [STRUCT('model1' AS model, false AS `group`), ('model2', true)] AS values
     UNION ALL
    SELECT 'import', [('model3', true), ('model4', false)] AS values;