PostgreSQL returning JSON array containing null value-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 that looks like [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;

Result:

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 count(right-table.column) to check if right table has matching rows or not; and build a case expression that returns [] or [{...}, {...}] depending on the count.

That being said, I would rather re-write your query like so:

select *, coalesce((
    select json_agg(row_to_json(user_vacation))
    from user_address
    join user_vacation on user_address.id = user_vacation.address_id
    where user_address.user_id = "user".id
), '[]') as vacations
from "user"
where place is not null