Query: appconfig_applications_for_codepipeline_pipeline

with pipeline_details as (
  select
    jsonb_array_elements(stages) as stage,
    jsonb_array_elements(stages -> 'Actions') as action
  from
    aws_codepipeline_pipeline
  where
    arn = $1
    and region = split_part($1, ':', 4)
    and account_id = split_part($1, ':', 5)
),
appconfig_actions as (
  select
    action -> 'Configuration' ->> 'Application' as application_id
  from
    pipeline_details
  where
    stage ->> 'Name' = 'Deploy'
    and action -> 'ActionTypeId' ->> 'Provider' = 'AppConfig'
)
select
  aa.arn as app_arn
from
  aws_appconfig_application aa
where
  aa.id in (select application_id from appconfig_actions);