Query: ebs_volumes_for_ec2_instance

with ec2_instances as (
  select
    arn,
    block_device_mappings,
    region,
    account_id
  from
    aws_ec2_instance
  where
    account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
    and arn = $1
  order by
    arn,
    region,
    account_id
),
ebs_volumes as (
  select
    arn,
    volume_id,
    region,
    account_id
  from
    aws_ebs_volume
  where
    account_id = split_part($1, ':', 5)
    and region = split_part($1, ':', 4)
  order by
    volume_id,
    region,
    account_id
)
select
  v.arn as volume_arn
from
  ec2_instances as i,
  jsonb_array_elements(block_device_mappings) as bd,
  ebs_volumes as v
where
  v.volume_id = bd -> 'Ebs' ->> 'VolumeId';