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

Question

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.

You could try array_remove like so: stackoverflow.com/questions/13122912/… — zedfoxus
– zedfoxus, Commented Jan 25, 2023 at 3:06

GuCier · Accepted Answer · 2023-01-25 03:36:06Z

4

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

answered Jan 25, 2023 at 3:36

GuCier

7,4851 gold badge32 silver badges40 bronze badges

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

1 Comment

RobertW Over a year ago

Simple but completely solved it! Thank you. Btw the last line in my query - ORDER BY date(m.date_created) DESC, m.date_created ASC is a technique I also learned here on StackOverflow. It groups photos in decreasing chronological order but then within each group (aka day) the photos are sorted in increasing order. Very much appreciate you all!!!

Atmo · Accepted Answer · 2023-01-25 03:09:51Z

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.

answered Jan 25, 2023 at 3:09

Atmo

4,2621 gold badge7 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related