[SOLVED] BigQuery and Logs Analysis Query 'Grouping by expressions of type STRUCT is not allowed' even when casting field to string

BigQuery and Logs Analysis Query 'Grouping by expressions of type STRUCT is not allowed' even when casting field to string

I'm trying to use the GCP logs analysis feature to get a breakdown of all log events in a logging bucket.

The fields I'm querying from the logs bucket dataset are:

log_name      STRING
json_payload  JSON

To ensure I'm working with all STRING data types, I select out the fields that I need into a temp table and query the temp table.

WITH log_breakdown AS (
  SELECT log_name,
         STRING(json_payload['@type']) AS log_type
  FROM myorg.global._Default._AllLogs
  WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
)

Per the below, all fields are of type STRING.

SELECT * FROM log_breakdown

But, if I try to group by log_type, I get the Grouping by expressions of type STRUCT is not allowed error.

SELECT log_name,
       log_type,
       COUNT(log_type) AS event_count
FROM log_breakdown
GROUP BY (log_name, log_type)
ORDER BY event_count desc

If each field in the temp table is of type STRING, why would I still get this error?

How can I aggregate on log_type to get a breakdown of my json_payload log events?

Solution

As mentioned by @Mikhail Berlyant, the issue can be resolved by using (log_name, log_type) that makes it implicitly of type struct . You can remove ( and ) and use GROUP BY log_name, log_type.