How to convert [NULL] to NULL in Postgres SQL statement?-CodePudding

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.

CodePudding user response：

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 ...

CodePudding user response：

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.