Transpose nested customDimension column data into rows Bigquery

Question

My BigQuery table looks like below

Fullvisitorid   CustomDimension.Index   CustomDimension.value
   123                1                       red
                      2                       blue
                      3                       green
   456                1                       red
                      3                       orange
                      4                       black

I want my final output to look like below

 Fullvisitorid   Color1     Color2
     123           red       green    
     456           red       orange

Below is the query I have written but I am getting an error "FUNCTION NOT FOUND: FIRST"

SELECT
  fullvisitorid,
  FIRST(IF(customDimensions.index=1, customDimensions.value, NULL)) color1,
  FIRST(IF(customDimensions.index=3, customDimensions.value, NULL)) color2
  FROM `my_table`
  cross join
  unnest(customDimensions) customDimensions,
  unnest(hits) hits
  where customDimensions.index in (1,3)
   group by fullvisitorid

I Found a similar question which helped me write my query:

[Transpose nested rows into columns in bigquery with google analytics data

I am not sure why I am getting an error on my query. I would really appreciate any help!

Thanks

Answer 1

You are using #standardSQL now - that's good.

Instead of FIRST() use ANY_VALUE().

I updated my answer in the referenced question accordingly:

• https://stackoverflow.com/a/29664156/132438

Answer 2

You can also create a User Defined Function and call it always you want a custom dimension:

SELECT
  fullvisitorid,
  PATH.CUSTOM_DIMENSION_BY_INDEX(1, h.customDimensions) AS color1,
  PATH.CUSTOM_DIMENSION_BY_INDEX(3, h.customDimensions) AS color2,
  FROM `my_table`
  cross join
  unnest(customDimensions) customDimensions,
  unnest(hits) hits
  where customDimensions.index in (1,3)
   group by fullvisitorid

Where PATH.CUSTOM_DIMENSION_BY_INDEX(index, h.customDimensions) is an UDF with format:

(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)

You can find more information about it here.