Join multiple table on array column

Question

I have many tables to join, some of them have joining columns as array,

When I tried with two tables it worked fine

select 
        tr.report_id,
        tr.sponsor_ids,
        array_agg(tsp.sponsor)
    --from public.tbl_report_page trp
    from  public.tbl_report tr --on trp.report_id = tr.report_id

    left join public.tbl_sponsor tsp on tsp.sponsor_id = any(tr.sponsor_ids)

    --left join public.tbl_tag tg on tg.tag_id = any(tr.tags)

    group by tr.report_id,tr.sponsor_ids--,trp.page_id
    having tr.report_id = 109

This works fine,

109 {1,2,3,4,5,6} "{sponsor_1,sponsor_2,sponsor_3,sponsor_4,sponsor_5,sponsor_6}"

but when I uncomment

left join public.tbl_tag tg on tg.tag_id = any(tr.tags)

getting lots of values in sponsors

109 {1,2,3,4,5,6}   "{sponsor_1,sponsor_1,sponsor_1,sponsor_2,sponsor_2,sponsor_2,sponsor_3,sponsor_3,sponsor_3,sponsor_4,sponsor_4,sponsor_4,sponsor_5,sponsor_5,sponsor_5,sponsor_6,sponsor_6,sponsor_6}"

I have to add more tables which contains ids as array.

You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. — Frank Heikens
– Frank Heikens, Commented Jul 13, 2023 at 9:46

Erwin Brandstetter · Accepted Answer · 2023-07-13 13:00:39Z

1

For starters, it's typically superior to replace those arrays with normalized many-to-many relationships in your relational design. See:

How to implement a many-to-many relationship in PostgreSQL?

While stuck with your current design, consider how joins can multiply rows:

Two SQL LEFT JOINS produce incorrect result

Your query probably works like this:

SELECT tr.report_id
     , tr.sponsor_ids
     , sp.sponsors
     , sp.tags
FROM   public.tbl_report tr
LEFT   JOIN LATERAL (
   SELECT array_agg(DISTINCT tsp.sponsor) AS sponsors
        , array_agg(DISTINCT tg.tag) AS tags
   FROM   public.tbl_sponsor tsp
   LEFT   JOIN public.tbl_tag tg ON tg.tag_id = ANY (tr.tags)
   WHERE  tsp.sponsor_id = ANY (tr.sponsor_ids)   
   ) sp ON true
WHERE  tr.report_id = 109;

Depending on your actual objective, more optimization may be possible.

This does not preserve original order of array items. See:

How can I match the items in an array column, with a different table in Postgres?

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

edited Jul 13, 2023 at 13:00

answered Jul 13, 2023 at 11:52

Erwin Brandstetter

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

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

2 Comments

Erwin Brandstetter Over a year ago

I might add more explanation after you clarify the exact objective.

Md. Parvez Alam Over a year ago

exactly I need this, but if using distinct then I can simply add distinct in my query as well to get the return

Collectives™ on Stack Overflow

Join multiple table on array column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related