Query

WITH 

raw_elements AS (
    SELECT
        courses.id AS course_id,
        courses.created_at,
        courses.name AS course_name,
        ARRAY_SIZE(courses.elements) AS nb_activities_in_the_course,
        flattened_elements.value['_id']['$oid']::varchar AS element_id,
        flattened_elements.value['collection']::varchar AS element_collection
    FROM courses,
    TABLE(FLATTEN(courses.elements)) flattened_elements
    ORDER BY courses.created_at DESC, courses.name, element_id
)
select 
    raw_elements.course_id,
    raw_elements.created_at,
    raw_elements.course_name,
    raw_elements.nb_activities_in_the_course,
    raw_elements.element_collection,
    raw_elements.element_id,
    coalesce(medias.name, questions.name, polls.name, external_contents.title, sheets.name) AS element_name,
    coalesce(medias.type, questions.qtype, polls.qtype, external_contents.content_type) AS element_type
from raw_elements
left join medias
    on raw_elements.element_id = medias.id
left join questions
    on raw_elements.element_id = questions.id
left join polls
    on raw_elements.element_id = polls.id
left join external_contents
    on raw_elements.element_id = external_contents.id
left join sheets
    on raw_elements.element_id = sheets.id
order by raw_elements.course_id

Description