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
I would go with a subquery in your case:
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.You can filter out nulls during the join process.
If none is returned, you should get a
NULL
instead of[NULL]