CODEWITHSUNDEEP
postgresqlaggregate-functions
Alex Suzuki
Alex Suzuki

Reputation: 1192

Merging arrays from array_agg into a single array

I have a data model that contains videos, shots and labels. A video consists of multiple shots (one-to-many), and a segment can contain multiple labels (many-to-many). I'm trying to create a search view where one can look up videos by label, videos should be returned if any shot contains the label. Shots and Labels are related through an association table label_occurrences (shot_id, label_id).

I got as far as being able to list the video segments and the labels contained therein using array_agg:

select
    video_id, video_name, shot_id, shot_labels
from
    videos
inner join shots using (video_id)
inner join (
    select shot_id, array_agg(labels.label_name) shot_labels
    from label_occurrences
    inner join labels using (label_id)
    group by shot_id
) x using (shot_id)
group by video_id, shot_id, shot_labels

VID1 | Video1 | Shot1 | {label1,label2}
VID1 | Video1 | Shot2 | {label3}

But what I'm after is union of the label arrays of each shot

VID1 | Video1 | { label1,label2,label3 }

Alas, my entirely too basic SQL skills start failing me, and the other articles so far don't seem to be doing the exact same as I do (I'm pretty sure the many-to-many relation is the culprit here).

Maybe someone can enlighten me?

Upvotes: 0

Views: 728

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

Basically just:

SELECT v.video_id, v.video_name, array_agg(l.label_name) AS shot_labels
FROM   videos            v
JOIN   shots             s  USING (video_id)
JOIN   label_occurrences lo USING (shot_id)
JOIN   labels            l  USING (label_id)
GROUP  BY 1;

The simplified GROUP BY assumes that video.video_id is the PRIMARY KEY of its table, so video_name is also covered.

Upvotes: 1

Related Questions