Platform - Google

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

select
  date,
  ad_id,
  all_conversions_by_conversion_date:"purchase"::number as purchase,
  all_conversions_value_by_conversion_date:"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 all_conversions
from <database>.<schema>.<table_name> t,
  lateral flatten(input => t.all_conversions_by_conversion_date) f;

select
  date,
  ad_id,
  all_conversions_by_conversion_date['purchase']::int as purchase,
  all_conversions_value_by_conversion_date['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.all_conversions_by_conversion_date as kv;

select
  date,
  ad_id,
  cast(json_value(all_conversions_by_conversion_date, '$.purchase') as int64) as purchase,
  cast(json_value(all_conversions_value_by_conversion_date, '$.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.all_conversions_by_conversion_date, concat('$.', k)) as int64) as conversions
from <project_id>.<dataset>.<table_name> t,
unnest(json_keys(t.all_conversions_by_conversion_date)) as k;