Merging arrays from array_agg into a single array

Question

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?

Can there be duplicate labels? And if so, do you want an array of unique labels? Any particular order? Your version of Postgres? — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 15, 2021 at 14:41

Erwin Brandstetter · Accepted Answer · 2021-12-15 14:44:21Z

1

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.

answered Dec 15, 2021 at 14:44

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging arrays from array_agg into a single array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related