Request to add columns origin_type and origin_domain to aws_cloudfront_distributions table

Hi CQ team,

Would it be possible to add columns origin_type and origin_domain to the table aws_cloudfront_distributions?

Can you link to the API docs of how to get those fields?

origin_domain you are already providing but in the distribution_config. However, as it’s a jsonb, it’s not possible to join on this field.

Even if selecting like:

jsonb_array_elements(distribution_config->'Origins'->'Items')->>'DomainName'

Because a distribution can have multiple origins, how would you want that represented?

Hm, maybe I’ll explain my use case.
I’d like to select distributions and their origins where origin_type = s3 and then join this information to the aws_s3_buckets on bucket_name.

That is a very interesting use case! I am going to look into it by spinning up a CloudFront distribution with S3 origin and get back to you…

this is my SQL query

SELECT id, jsonb_array_elements(distribution_config->'Origins'->'Items')->>'DomainName' AS origin_name, domain_name 
FROM aws_cloudfront_distributions

Here is a query that is able to join S3 origins to an S3 bucket:

SELECT ORIGIN_ID,
    ORIGIN_NAME,
    DOMAIN_NAME,
    AWS_S3_BUCKETS.*
FROM
    (SELECT ID as ORIGIN_ID,
            JSONB_ARRAY_ELEMENTS(DISTRIBUTION_CONFIG -> 'Origins' -> 'Items') ->> 'DomainName' AS ORIGIN_NAME,
            DOMAIN_NAME
        FROM AWS_CLOUDFRONT_DISTRIBUTIONS) AS CLOUDFRONT
LEFT JOIN AWS_S3_BUCKETS ON CLOUDFRONT.ORIGIN_NAME = AWS_S3_BUCKETS.NAME || '.s3.' || AWS_S3_BUCKETS.REGION || '.amazonaws.com'