Bigquery ads_insights query latest batch return Column website_ctr of type ARRAY cannot be used in SELECT DISTINCT

bigquery
facebookads
#1

I am syncing all Facebook insights to BigQuery. This is an append only table which means that I have to fetch the latest batch. So I’m trying to select the latest batch by using campaign_id as the primary key. But when I try to do that with the query from the docs I get this error. Does anyone know how to query the latest batch from BigQuery?

Here is the query issue from BigQuery
Column website_ctr of type ARRAY cannot be used in SELECT DISTINCT

Here is the query I’m using

SELECT DISTINCT o.* FROM `xxx.facebook_ads.ads_insights` o
INNER JOIN (
     SELECT campaign_id,
            MAX(_sdc_sequence) AS seq,
            MAX(_sdc_batched_at) AS batch
    FROM `xxx.facebook_ads.ads_insights`
    GROUP BY campaign_id) oo
ON o.campaign_id = oo.campaign_id
AND o._sdc_sequence = oo.seq
AND o._sdc_batched_at = oo.batch
#2

Looks like you adapted that query from the docs. I’m no expert, but I think that, since I don’t see website_ctr in the query, it’s slipping in via o.*. Could you create a view using the UNNEST operator to take that data out of the array? I did that for a different use case, documented in a blog post, and that worked for my needs.