![]() ![]() or use array-to-array concatenation: WITH cte_tags AS ( If you really want an empty array instead of NULL in your results, you can use the COALESCE function.: WITH cte_tags AS ( Instead of an array with a single element of NULL, you will now get NULL instead of an array. LEFT JOIN cte_tags ON cte_tags.object_id = objects.id INNER JOIN tags ON tags.id = taggings.tag_id Perhaps this answer is a little late, but I wanted to share with you that another querying strategy is possible as well: performing the aggregation in a a separate (common) table expression. But you need to apply that to the array elements instead of the array, which, in your case, would not provide a solution. Incidentally, the statement in the documentation about using coalesce() is a bit crummy: what is meant is that if you do not want NULL as a result, you can use coalesce() to turn that into a 0 or some other output of your choosing. The trick here is that the first (and only) element in a array has a length of 0, so if any data is returned from tags you return the aggregate, otherwise construct an empty array of the right type. This assumes that the tags are of text type (or any of its variants) modify the cast as required. LEFT JOIN tags ON tags.id = taggings.tag_id LEFT JOIN taggings ON objects.id = taggings.object_id If you want to convert that to an empty array, then you need to do some minor magic: SELECT objects.id,ĬASE WHEN length((array_agg(tags.tag))) > 0 The documentation says that an array containing NULL is returned. So in your case, just replace array_agg(tags.tag) You can identify an object with no tags (or in general, tell when a LEFT JOIN found no matches) by checking whether the field on the other side of the join condition is null. Your application logic and/or integrity constraints may not allow this second case, but that's all the more reason not to suppress a null tag if it does manage to sneak in. ![]() You might be tempted to blindly replace with in the output, but then you lose the ability to distiguish between objects with no tags and tagged objects where tags.tag is null. This does not apply to your query, because of the way a LEFT JOIN behaves - when it finds zero matching rows, it returns one row, filled with nulls (and the aggregate of one null row is an array with one null element). The docs say that when you are aggregating zero rows, then you get a null value, and the note about using COALESCE is addressing this specific case. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |