Query: s3_buckets_for_codepipeline_pipeline

select
  arn as s3_bucket_deploy_arn
from
  aws_s3_bucket
where
  name in
  (
    select
      a -> 'Configuration' ->> 'BucketName' as bucket_name
    from
      aws_codepipeline_pipeline,
      jsonb_array_elements(stages) as s,
      jsonb_array_elements(s -> 'Actions') as a
    where
      s ->> 'Name' = 'Deploy'
      and a -> 'ActionTypeId' ->> 'Provider' = 'S3'
      and arn = $1
      and region = split_part($1, ':', 4)
      and account_id = split_part($1, ':', 5)
    union
    select
      a -> 'Configuration' ->> 'S3Bucket' as bucket_name
    from
      aws_codepipeline_pipeline,
      jsonb_array_elements(stages) as s,
      jsonb_array_elements(s -> 'Actions') as a
    where
      s ->> 'Name' = 'Source'
      and a -> 'ActionTypeId' ->> 'Provider' = 'S3'
      and arn = $1
      and region = split_part($1, ':', 4)
      and account_id = split_part($1, ':', 5)
  );