CODEWITHSUNDEEP
google-bigquerylooker-studio
Geoff Coco
Geoff Coco

Reputation: 1

Duplicate alias error with BigQuery connection to table with arrays

Inside Google Data Studio (now called Looker Studio) I'm getting BigQuery error:

Query error: Duplicate alias **paid_per_stats** found at [line number]

I am connecting to a table with nested arrays - see schema snippet below. In the data studio BigQuery connector I create two alias (for period 1 and period 2), with distinct alias names. But when I create a calculated field in the report comparing fields from the two periods, the table fails to load giving the duplicate alias error.

connector code:

select 
a.period_start_dt = date(@p1) as is_p1,
case when a.period_start_dt = date(@p1) then a.paid_skus else null end as p1_paid_skus, 
case when a.period_start_dt = date(@p2) then a.paid_skus else null end as p2_paid_skus

from campaign_kpis a
where (a.period_start_dt = date(@p1) or a.period_start_dt = date(@p2))

a.paid_skus has the schema:

-- other stuff
period_start_dt date,
paid_skus array<struct<
    sku_id string,
    paid_per_stats array<struct<
        sample_dt date,
        kpi1 int,
        kpi2 int
        >>
    >>

example calculated field:

(sum(p1_paid_skus.paid_per_stats.kpi1)-sum(p2_paid_skus.paid_per_stats.kpis))/sum(p2_paid_skus.paid_per_stats.kpi1)

everything works fine if i don't use p1 and p2 in the same calculated field. It's as if data studio isn't using the full path to the field as the alias, just the "last mile" any ideas?

Upvotes: 0

Views: 154

Answers (1)

Geoff Coco
Geoff Coco

Reputation: 1

At this point this appears to be a bug in the platform. I worked around the bug in the Data Studio connection sql, by unnesting the arrays and then renesting (using array_agg) to the original schema with a different name for p1 and p2 inner arrays. Gross but worked.

Upvotes: 0

Related Questions