GBQ Arrays examples with Dimensions #sql

arrays.sql

-- example 1
-- unnest an array in line so to perform an aggregation

select 
  id, 
  (SELECT MAX(e) from UNNEST(email) e) as first_email
from `dimensions-ai.data_analytics.researchers` r
WHERE ARRAY_LENGTH(email) > 0
LIMIT 10


-- example 2
-- get the first item of an array
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#offset_and_ordinal

select 
  id, 
  email[ORDINAL(1)] as first_email
from `dimensions-ai.data_analytics.researchers` r
WHERE ARRAY_LENGTH(email) > 0
LIMIT 10


-- example 3
-- turn into a string

select 
  id, 
  ARRAY_TO_STRING(email, "; ")
from `dimensions-ai.data_analytics.researchers` r
WHERE ARRAY_LENGTH(email) > 0
LIMIT 10
                
                
-- example 4 
-- turn into a string via aggregation (eg for nested structures) 
WITH s1 AS (
SELECT
  p.id, 
  STRING_AGG(cat.name) as x
FROM
  `dimensions-ai.data_analytics.publications` p
  LEFT JOIN UNNEST(category_for.first_level.full) cat
WHERE
  p.id= "pub.1132560058"
GROUP BY p.id  
  ) 
  
SELECT * from s1