Google BigQuery standart SQL insert nested row which has nested rows

Question

I'm trying to figure out how to run an INSERT (i guess update?) query to insert into rows which are nested, but latter rows also have a nested schema in themselves.

For instance i have something like

What would a general template query look like?

I know that for the lines it would look something like

Please try to ask a more specific question, you'll get a more specific answer. Initial state, end results expected? — Felipe Hoffa
– Felipe Hoffa, Commented Oct 1, 2018 at 9:46

Vasil Mitov · Accepted Answer · 2018-10-01 12:15:48Z

1

Managed to solve it with:

UPDATE test 
SET lines=ARRAY(
SELECT line FROM UNNEST(lines) AS line
UNION ALL
SELECT (
'::someValue::,
ARRAY<STRUCT<STRING,STRING,FLOAT64>>[('someId','someString',1.0)]
)
WHERE id='someId'

answered Oct 1, 2018 at 12:15

Vasil Mitov

676 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google BigQuery standart SQL insert nested row which has nested rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related