PostgreSQL returning JSON array containing null value

Question

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]

sorry, fixed it.

junius10
– junius10

2022-05-24 21:00:08 +00:00
Commented May 24, 2022 at 21:00 — junius10
– junius10, Commented May 24, 2022 at 21:00

Salman Arshad · Accepted Answer · 2022-05-25 08:04:59Z

1

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

edited May 25, 2022 at 8:04

answered May 24, 2022 at 21:05

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

2 Comments

MatBailie Over a year ago

Wouldn't a left join on an aggregate sub-query be tidier than a correlated sub-query?

Salman Arshad Over a year ago

Tidier is opinion based but it'll work too, yes.

Collectives™ on Stack Overflow

PostgreSQL returning JSON array containing null value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related