UNNEST(hit.eCommerceAction), Google Bigquery

Question

I used the same logic to unnest hit.eCommerceAction, but it's not working as other fields. Any help on this problem? Also, Is the max(if()) function the right function to use to get two hits.customeDimenison.value?

SELECT
 Date
  ,COUNT(DISTINCT FULLVISITORID)
 , product.v2ProductCategory

  ,max(if(customDimensions.index=2, customDimensions.value,null))  as dest
  ,max(if(customDimensions.index=21, customDimensions.value,null))  as pax
 ,eCommerceAction.action_type
 ,product.v2ProductName

FROM `table` as t
  CROSS JOIN UNNEST(hits) AS hit
  CROSS JOIN UNNEST(hit.customDimensions) AS customDimensions
  CROSS JOIN UNNEST(hit.eCommerceAction) as eCommerceAction
  CROSS JOIN UNNEST(hit.product) AS product
GROUP BY 
      Date
      ,product.v2ProductCategory
    ,eCommerceAction.action_type
,product.v2ProductName

The Error code I am getting is Error: Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2017-01-11T01:03:21

I can reproduce the error with a simpler query:

#standardSQL
SELECT DISTINCT hit.eCommerceAction.action_type 
FROM `73156703.ga_sessions_20170109` t
  , UNNEST(hits) hit
  , UNNEST(hit.customDimensions) customDimensions
  , UNNEST(hit.eCommerceAction) as eCommerceAction

The issue here is that eCommerceAction is not a REPEATED record, hence there is no array to UNNEST.

Fixed query:

#standardSQL
SELECT DISTINCT hit.eCommerceAction.action_type 
FROM `ga_sessions_20170109`  t
  , UNNEST(hits) hit
  , UNNEST(hit.customDimensions) customDimensions

UNNEST(hit.eCommerceAction), Google Bigquery

1 Answers