Description

The query explodes all the tags associated to a question, gets the name of the tag and re-aggregate the data at the level of the question

Query

WITH initial AS (
    SELECT
        questions.id,
        questions.name,
        ARRAY_AGG(tags.name) AS tag_names
    FROM qwnogpy_ww21770_datasharing.core.questions questions
    LEFT JOIN LATERAL FLATTEN(input => questions.tag_ids) flattened_tags
    LEFT JOIN qwnogpy_ww21770_datasharing.core.tags tags
        ON flattened_tags.value::VARCHAR = tags.id
    WHERE ARRAY_SIZE(questions.tag_ids) > 0
    GROUP BY 1,2
)
SELECT *
FROM initial;