Posted in bigquery

BigQuery : What is STRUCT in Google BigQuery

   January 30, 2023  Leave a Comment on BigQuery : What is STRUCT in Google BigQuery
Google Big Query @ Freshers.in

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];
SQL
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;
SQL
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"
...
Bash
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;
SQL
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"
...
SQL
Post Views: 98

Related Posts

Author: user
Website

Leave a Reply