This query fetches the courses that are part of paths (”steps” of a path).

Query

WITH

initial_courses_in_paths AS (
    SELECT
        paths.id AS path_id,
        paths.name AS path_name,
        paths.author AS path_author_id,
        paths.owner_group AS path_group_owner_id,
        paths.co_authors AS co_author_ids,
        paths.steps,
        steps.value['courseId']['$oid']::varchar AS course_id
    FROM qwnogpy_ww21770_datasharing.core.paths paths,
    TABLE(FLATTEN(paths.steps)) steps
    WHERE course_id IS NOT null
),

raw_co_authors AS (
    SELECT
        paths.path_id, 
        paths.co_author_ids,
        coauthors.value::varchar AS co_author_id
    FROM initial_courses_in_paths paths,
    TABLE(FLATTEN(paths.co_author_ids)) coauthors
    WHERE co_author_id IS NOT null
),

co_authors_agg AS (
    SELECT 
        raw_co_authors.path_id,
        ARRAY_AGG(concat(users.first_name, ' ', users.last_name)) AS co_author_names
    FROM raw_co_authors
    LEFT JOIN qwnogpy_ww21770_datasharing.core.users users
        ON raw_co_authors.co_author_id = users.id
    GROUP BY 1
),

courses_in_paths AS (
    SELECT
        DISTINCT initial_courses_in_paths.path_id, 
        initial_courses_in_paths.path_name, 
        initial_courses_in_paths.path_author_id,
        concat(path_author.first_name, ' ', path_author.last_name) AS path_author_name, 
        initial_courses_in_paths.co_author_ids,
        co_authors_agg.co_author_names,
        initial_courses_in_paths.path_group_owner_id,
        path_groups.name AS path_group_owner_name,
        courses.id AS course_id, 
        courses.name AS course_name,
        courses.external_content['externalPlatform']::varchar AS external_platform,
    FROM initial_courses_in_paths
    INNER JOIN qwnogpy_ww21770_datasharing.core.courses courses
        ON initial_courses_in_paths.course_id = courses.id
        --AND (courses.external_platform LIKE '%Bookboon%')
    LEFT JOIN qwnogpy_ww21770_datasharing.core.users AS path_author
        ON  initial_courses_in_paths.path_author_id = path_author.id
    LEFT JOIN qwnogpy_ww21770_datasharing.core.groups AS path_groups
        ON  initial_courses_in_paths.path_group_owner_id = path_groups.id
    LEFT JOIN co_authors_agg
        ON initial_courses_in_paths.path_id = co_authors_agg.path_id
)

SELECT * FROM courses_in_paths

Description