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