skip to Main Content

In Postgres, I set out to write a SQL statement that would return various fields from one table, along with a column containing an array of tag strings that come from another table. I’ve made quite good progress with this code:

SELECT p.photo_id, p.name, p.path, array_agg(t.tag) as tags FROM photos p
JOIN users u USING (user_id)
LEFT JOIN photo_tags pt USING (photo_id)
LEFT JOIN tags t USING (tag_id)
WHERE u.user_id = 'some_uuid'
GROUP BY p.photo_id, p.name, p.path
ORDER BY date(p.date_created) DESC, p.date_created ASC

Everything is working exactly like I intended except for one thing: If a given photo has no tags attached to it then this is being returned: [NULL]

I would prefer to return just NULL rather than null in an array. I’ve tried several things, including using coalesce and ifnull but couldn’t fix things precisely the way I want.

Not the end of the world if an array with NULL is returned by the endpoint but if you know a way to return just NULL instead, I would appreciate learning how to do this.

2

Answers


  1. I would go with a subquery in your case:

    SELECT p.photo_id, p.name, p.path, agg_tags as tags
    FROM photos p
    JOIN users u USING (user_id)
    LEFT JOIN photo_tags pt USING (photo_id)
    LEFT JOIN (
        SELECT tag_id, array_agg(tag) AS agg_tags
        FROM tags
        GROUP BY tag_id
    ) t USING (tag_id)
    WHERE u.user_id = 'some_uuid'
    ORDER BY date(p.date_created) DESC, p.date_created ASC
    

    You did not post many information about your schema, table size and so on but a LATERAL join could be an option to add on the above syntax.

    Login or Signup to reply.
  2. You can filter out nulls during the join process.
    If none is returned, you should get a NULL instead of [NULL]

    SELECT array_agg(t.tag) filter (where t.tag is not null) as tags
    FROM ...
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search