Postgresql returning json field with null values-CodePudding

I have the following query on postgresql, I have the vacations columns that I display an array of objects, but some columns don't have values and will return null values, but the problem is, it will always return a array with the value "[NULL]", Is it possible to return only an empty []?

SELECT 
    u."name",
    u.email,
    to_json(array_agg(row_to_json(uv))) as vacations
from public."user" as u 
join user_address ua on ua.user_id = u.id 
left join user_vacation uv on uv.address_id = ua.id 
where u.place is not null 
group by 
    u."name",
    u.email;

name       | email            | vacations
some_name  | [email protected]   | [{"id": 1, "area": "description"}]
some_name  | [email protected]   | [{"id": 1, "area": "description"}]
some_name  | [email protected]   | [null]
some_name  | [email protected]   | [null]

CodePudding user response：

You can use a case expression to check if count of user_vacation is zero or more:

case when count(uv.id) = 0 then '[]' else to_json(array_agg(row_to_json(uv))) end