Set column of Array to variable value in bigquery scripting

Question

I'm trying to get the value of multiple columns in an array and set them as a variable that can be used in the loop to do something else. Thanks.

DECLARE the_array ARRAY<STRUCT<value1 STRING,value2 STRING>>;

SET the_array = (
  SELECT ARRAY_AGG(STRUCT(value1,value2))
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);




LOOP
  SET i = i + 1;

  SET var1 = the_array[ORDINAL(????)]; // what do I do here?


  SET var2 = the_array[ORDINAL(???)]; // what do I do here?



  IF i > ARRAY_LENGTH(the_array) THEN 
    LEAVE; 
  END IF;
 
 insert into `project.dataset.other_table` values(var1,var2);


END LOOP;

Mikhail Berlyant · Accepted Answer · 2021-01-21 15:32:12Z

3

If you are looking for correct way of achieving above result - do it in set based way which is most effective way of expressing your logic in SQL

INSERT INTO `project.dataset.other_table`
SELECT value1, value2
FROM `project.dataset.table`
WHERE somthing = 'somthing'

Meantime, if purpose of your question is to learn BigQuery Scripting - see below example

DECLARE the_array ARRAY<STRUCT<value1 STRING,value2 STRING>>;
DECLARE i INT64 DEFAULT 0;
DECLARE var1, var2 STRING;

SET the_array = (
  SELECT ARRAY_AGG(STRUCT(value1,value2))
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);

LOOP
  SET i = i + 1;

  IF i > ARRAY_LENGTH(the_array) THEN 
    LEAVE; 
  END IF;

  SET var1 = the_array[ORDINAL(i)].value1; 
  SET var2 = the_array[ORDINAL(i)].value2; 
 
 insert into `project.dataset.other_table` values(var1,var2);

END LOOP;

answered Jan 21, 2021 at 15:32

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

minobu Over a year ago

Yes, I'm trying to learn BigQuery scripting, that second example is what I was after. Can you recommend any good learning recourses in addition to Google Docs for scripting? Many thanks.

Collectives™ on Stack Overflow

Set column of Array to variable value in bigquery scripting

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related