Query: cloud_billing_report_cost_by_label_dashboard_monthly_cost

with filtered as (
  select 
    date_trunc('month', usage_start_time) as month,
    cost, 
    project_labels, 
    labels, 
    system_labels, 
    project_id
  from 
    gcp_billing_report
  where 
    ('all' in ($1) or project_id in $1)
),
picked as (
  select 
    f.month, 
    f.cost,
    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

  select 
    f.month, 
    f.cost,
    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

  select 
    f.month, 
    f.cost,
    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
  strftime(month, '%b %Y') as "Month",
  label_value              as "Series",
  round(sum(cost), 2)      as "Total Cost"
from 
  picked
where 
  label_value is not null and label_value <> ''
group by 
  month, 
  label_value
having 
  sum(cost) > 0
order by 
  month, 
  sum(cost) desc;