UNNEST(hit.eCommerceAction), Google Bigquery

Asked at 2017-01-11 00:00:37Z
  • 5 Subscribers
  • 114 Views
2

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


1 answers in total

3
Felipe Hoffa Posted at 2017-01-11 01:03:21Z

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

Answer this questsion