Query: ec2_instance_block_device_mapping

with volume_details as (
select
  p -> 'Ebs' ->> 'VolumeId'  as "Volume ID",
  p ->> 'DeviceName'  as "Device Name",
  p -> 'Ebs' ->> 'AttachTime' as "Attach Time",
  p -> 'Ebs' ->> 'DeleteOnTermination' as "Delete On Termination",
  p -> 'Ebs' ->> 'Status'  as "Status",
  arn
from
  aws_ec2_instance,
  jsonb_array_elements(block_device_mappings) as p
where
  arn = $1
)
select
  "Volume ID",
  "Device Name",
  "Attach Time",
  "Delete On Termination",
  "Status",
  v.arn as "Volume ARN"
from
  volume_details as vd
  left join aws_ebs_volume v on v.volume_id = vd."Volume ID"
where
  v.volume_id in (select "Volume ID" from volume_details)