BigQuery Introduces Enhanced SQL Group By Features for STRUCTs and ARRAYs

Google has announced the general availability (GA) of several powerful SQL features in BigQuery, enhancing data grouping capabilities and query flexibility.

GROUP BY STRUCT Feature

The GROUP BY STRUCT feature and the SELECT DISTINCT clause are now fully supported in BigQuery. This implementation follows specific grouping rules:

• A STRUCT type is groupable if all its field types are groupable
• STRUCT types can only be grouped in a GROUP BY clause or a SELECT DISTINCT clause
• Two structs are considered in the same group when either:

• Both structs are NULL
• All corresponding field values between the structs belong to the same groups

GROUP BY ARRAY Feature

Similarly, BigQuery now fully supports GROUP BY ARRAY functionality and the SELECT DISTINCT clause with arrays. The grouping behavior follows these principles:

• An ARRAY type is groupable when its element type is groupable
• ARRAY types can only be grouped in a GROUP BY clause or a SELECT DISTINCT clause
• Two arrays are considered in the same group when either:

• Both arrays are NULL
• The arrays have identical element counts and all corresponding elements belong to the same groups

GROUP BY ALL Clause

The new GROUP BY ALL clause introduces intelligent grouping by automatically inferring grouping keys from the SELECT items. This feature excludes certain SELECT items from the inference:

• Expressions containing aggregate functions
• Expressions containing window functions
• Expressions that don't reference names from the FROM clause, including:

• Constants
• Query parameters
• Correlated column references
• Expressions only referencing GROUP BY keys inferred from other SELECT items