The STRUCT expression in BigQuery allows you to create a structure or a nested field in your query result. This can be useful when you need to group multiple columns into a single value. The basic syntax for the STRUCT expression is as follows:
SELECT STRUCT([column1], [column2], ...) AS [struct_alias]
FROM [table_name];
For example, consider a table named freshers_table with columns first_name, second_name, and salary. If you want to create a structure that groups first_name and second_name as a single value, you can use the following query:
SELECT STRUCT(first_name, second_name) AS full_name, salary
FROM freshers_in_table;
This query will return a result set with two columns: full_name and salary. The full_name column will contain a structure that groups the values of first_name and second_name. The result of this query will look something like this:
full_name column3
---------------------------- ------
{"first_name": "Sam", "second_name": "Peter"} "$35000"
{"first_name": "John", "second_name": "Williams"} "$45000"
...It’s important to note that you can use the AS clause to give a custom name to the structure, in this case struct_value. You can also use the WITHIN clause to specify a nested field within the structure. For example:
SELECT STRUCT(column1 WITHIN struct_value AS field1, column2 WITHIN struct_value AS field2) AS struct_value, column3
FROM my_table;
This query will return a result set with the same two columns, but the values within the struct_value column will be nested under field1 and field2. The result of this query will look something like this:
struct_value column3
---------------------------- ------
{"field1": "value1", "field2": "value2"} "value3"
{"field1": "value4", "field2": "value5"} "value6"
...
BigQuery import urls to refer
