I have a table with two columns: group_id, students (Array of student). Each student have a two attribute: name and last_name. How do you swap name with last_name for each student?
Students table: group_id | students.name | students.last_name --------------------------------------------- 1 | Perez | Juan --------------------------------------------- 2 | Caicedo | Pedro
I need to swapping students.name to students.last_name, any idea?
Advertisement
Answer
Finally, thanks to Mikhail Berlyant who answered this question (Update values in struct arrays in BigQuery), i was able to exchange attributes on update clause.
UPDATE `Students` s SET students = ARRAY( SELECT AS STRUCT last_name, name FROM s.students ) WHERE TRUE;
Too, i was able to exchange attributes on update clause with if condition for example.
UPDATE `Students` s SET students = ARRAY( SELECT AS STRUCT IF(CONTAINS_SUBSTR(name, 'Pedro'), name, last_name), IF(CONTAINS_SUBSTR(last_name, 'Caicedo'), last_name, name) FROM s.students ) WHERE TRUE;
It’s very important the position in the STRUCT, in my case name is first and last_name is second, for achieve the exchange just by changing its position the objective is achieved.