Trying to UNNEST timestamp array field, but need to GROUP BY

Question

I have a repeated field of type TIMESTAMP in a BigQuery table. I am attempting to UNNEST this field. However, I must group or aggregate the field in order. I am not knowledgable with SQL, so I could use some help. The code snippet is part of a larger query that works when substituting subscription.future_renewal_dates with GENERATE_TIMESTAMP_ARRAY

subscription.future_renewal_dates is ARRAY<TIMESTAMP>

The TIMESTAMP array is unique (recurring subscriptions) and cannot be generated using GENERATE_TIMESTAMP_ARRAY, so I have to generate the dates before uploading to BigQuery. UDF is too much.

SELECT
   subscription.amount AS subscription_amount,
   subscription.status AS subscription_status,
   "1" AS analytic_name,
   ARRAY ( 
   SELECT
      AS STRUCT FORMAT_TIMESTAMP("%x", days) AS type_value, subscription.amount AS analytic_name 
   FROM
      UNNEST(subscription.future_renewal_dates) as days 
   WHERE
      (
         days >= TIMESTAMP("2019-06-05T19:30:02+00:00") 
         AND days <= TIMESTAMP("2019-08-01T03:59:59+00:00") 
      )
) AS forecast 
   FROM
      `mydataset.subscription` AS subscription 
   GROUP BY
      subscription_amount,
      subscription_status,
      analytic_name

Cannot figure out how to successfully unnest subscription.future_renewal_dates without error 'UNNEST expression references subscription.future_renewal_dates which is neither grouped nor aggregated'

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-06-05T23:19:41

When you do GROUP BY - all expressions, columns in the SELECT (except those in GROUP BY list) should be used with some aggregation function - which you clearly do not have. So you need to decide what it is that you actually trying to achieve here with that grouping

Below is the option I think you had in mind - though it can be different - but at least you have an idea on how to fix it

SELECT
   subscription.amount AS subscription_amount,
   subscription.status AS subscription_status,
   "1" AS analytic_name,
   ARRAY_CONCAT_AGG( ARRAY ( 
   SELECT
      AS STRUCT FORMAT_TIMESTAMP("%x", days) AS type_value, subscription.amount AS analytic_name 
   FROM
      UNNEST(subscription.future_renewal_dates) as days 
   WHERE
      (
         days >= TIMESTAMP("2019-06-05T19:30:02+00:00") 
         AND days <= TIMESTAMP("2019-08-01T03:59:59+00:00") 
      )
)) AS forecast 
   FROM
      `mydataset.subscription` AS subscription 
   GROUP BY
      subscription_amount,
      subscription_status,
      analytic_name

Trying to UNNEST timestamp array field, but need to GROUP BY

1 Answers