Query: wellarchitected_workload_risks_by_pillar

select
  p ->> 'PillarName' as pillar_name,
  sum((p -> 'RiskCounts' ->> 'HIGH')::int) as high_risks,
  sum((p -> 'RiskCounts' ->> 'MEDIUM')::int) as medium_risks,
  sum((p -> 'RiskCounts' ->> 'NONE')::int) as no_improvements_risks,
  sum((p -> 'RiskCounts' ->> 'NOT_APPLICABLE')::int) as not_applicable_risks
from
  aws_wellarchitected_lens_review as r,
  jsonb_array_elements(r.pillar_review_summaries) as p
where
  r.workload_id = $1
  and r.lens_arn = any(string_to_array($2, ','))
group by
  pillar_name
order by
  pillar_name