Platform Performance - Facebook

{
  "purchase": 12,
  "add_to_cart": 41
}

select
  date,
  ad_id,
  click_1d:"purchase"::number as purchase_click_1d,
  click_1d_value:"purchase"::float as purchase_value_click_1d
from <database>.<schema>.<table_name>;

select
  t.date,
  t.ad_id,
  f.key as conversion_event,
  f.value::number as conversions_click_1d
from <database>.<schema>.<table_name> t,
  lateral flatten(input => t.click_1d) f;

select
  date,
  ad_id,
  click_1d['purchase']::int as purchase_click_1d,
  click_1d_value['purchase']::decimal(18,4) as purchase_value_click_1d
from <database>.<schema>.<table_name>;

select
select
  t.date,
  t.ad_id,
  kv.key as conversion_event,
  kv.value::int as conversions_click_1d
from <database>.<schema>.<table_name> t,
  t.click_1d as kv;

select
  date,
  ad_id,
  cast(json_value(click_1d, '$.purchase') as int64) as purchase_click_1d,
  cast(json_value(click_1d_value, '$.purchase') as float64) as purchase_value_click_1d
from <project_id>.<dataset>.<table_name>;

select
  t.date,
  t.ad_id,
  k as conversion_event,
  cast(json_value(t.click_1d, concat('$.', k)) as int64) as conversions_click_1d
from <project_id>.<dataset>.<table_name> t,
unnest(json_keys(t.click_1d)) as k;