Platform - Snapchat

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

select
  date,
  ad_id,
  view_1_day:"purchase"::number as purchase,
  view_1_day_value:"purchase"::float as purchase_value
from <database>.<schema>.<table_name>;

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

select
  date,
  ad_id,
  view_1_day['purchase']::int as purchase,
  view_1_day_value['purchase']::decimal(18,4) as purchase_value
from <database>.<schema>.<table_name>;

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

select
  date,
  ad_id,
  cast(json_value(view_1_day, '$.purchase') as int64) as purchase,
  cast(json_value(view_1_day_value, '$.purchase') as float64) as purchase_value
from <project_id>.<dataset>.<table_name>;

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