CODEWITHSUNDEEP
google-bigquerybigquery-udf
element
element

Reputation: 79

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.

Upvotes: 3

Views: 7110

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

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;

Upvotes: 3

Related Questions