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
  )