Query: wellarchitected_workload_milestone_lens_review_risk_counts

-- Get current version's lens review
select
  r.workload_id,
  r.milestone_number,
  r.lens_arn,
  jsonb_pretty(r.risk_counts) as lens_risk_counts,
  p ->> 'PillarId' as pillar_id,
  p ->> 'PillarName' as pillar_name,
  p -> 'RiskCounts' as pillar_risk_counts
from
  aws_wellarchitected_lens_review as r,
  jsonb_array_elements(pillar_review_summaries) as p
union
-- Get past milestone lens reviews
select
  r.workload_id,
  r.milestone_number,
  r.lens_arn,
  jsonb_pretty(r.risk_counts) as lens_risk_counts,
  p ->> 'PillarId' as pillar_id,
  p ->> 'PillarName' as pillar_name,
  p -> 'RiskCounts' as pillar_risk_counts
from
  aws_wellarchitected_milestone as m
  left join
    aws_wellarchitected_lens_review as r
    on m.milestone_number = r.milestone_number,
    jsonb_array_elements(pillar_review_summaries) as p