Query: kinesis_streams_for_api_gatewayv2_api

with filtered_api as (
  select
    api_id,
    account_id,
    region
  from
    aws_api_gatewayv2_api
  where
    api_id = $1
),
filtered_integration as (
  select
    request_parameters,
    account_id,
    region,
    api_id
  from
    aws_api_gatewayv2_integration
  where
    integration_subtype like '%Kinesis-%'
    and api_id = $1
    and account_id = (
      select
        account_id
      from
        filtered_api
    )
    and region = (
      select
        region
      from
        filtered_api
    )
),
filtered_kinesis_stream as (
  select
    stream_arn,
    stream_name
  from
    aws_kinesis_stream
  where
    stream_name = (
      select
        request_parameters ->> 'StreamName'
      from
        filtered_integration
    )
    and account_id = (
      select
        account_id
      from
        filtered_integration
    )
    and region = (
      select
        region
      from
        filtered_integration
    )
)
select
  s.stream_arn as kinesis_stream_arn
from
  filtered_integration i
  join filtered_kinesis_stream s on i.request_parameters ->> 'StreamName' = s.stream_name
  join filtered_api a on a.api_id = i.api_id;