Query: cloud_billing_report_cost_by_label_dashboard_total_cost

with filtered as (
  select 
    cost, 
    currency, 
    project_id, 
    project_labels, 
    labels, 
    system_labels
  from gcp_billing_report
  where ('all' in ($1) or project_id in $1)
),
picked as (
  -- project labels
  select 
    f.cost, 
    f.currency,
    json_extract_string(f.project_labels, '$[' || i || '].value') as label_value
  from 
    filtered f,
    generate_series(0, 200) as gs(i)
  where $2 = 'project_labels'
    and f.project_labels is not null
    and i < json_array_length(f.project_labels)
    and json_extract_string(f.project_labels, '$[' || i || '].key') = $3

  union all

  -- resource labels
  select 
    f.cost, 
    f.currency,
    json_extract_string(f.labels, '$[' || i || '].value')
  from 
    filtered f,
    generate_series(0, 200) as gs(i)
  where $2 = 'labels'
    and f.labels is not null
    and i < json_array_length(f.labels)
    and json_extract_string(f.labels, '$[' || i || '].key') = $3

  union all

  -- system labels
  select 
    f.cost, 
    f.currency,
    json_extract_string(f.system_labels, '$[' || i || '].value')
  from 
    filtered f,
    generate_series(0, 200) as gs(i)
  where $2 = 'system_labels'
    and f.system_labels is not null
    and i < json_array_length(f.system_labels)
    and json_extract_string(f.system_labels, '$[' || i || '].key') = $3
)
select
  'Total Cost (' || max(currency) || ')' as label,
  round(sum(cost), 2) as value
from 
  picked
where 
  label_value is not null and label_value <> '';