Im using jsob_build_object function to generate json from data in my table.
select json_build_object('name', p.name, 'birthday', p.birthday)
FROM Person p limit 2
The result is:
{"name":"John", "birthday", "2000-01-01"}
{"name":"George", "birthday", "null"}
Now as you can see in second row birthday is null. In that case I would like that JSON field (birthday) to not be present there so the result would be:
{"name":"John", "birthday", "2000-01-01"}
{"name":"George"}
Is it possible?
Use json_strip_nulls()
select json_strip_nulls(json_build_object('name', p.name, 'birthday', p.birthday))
FROM person p
limit 2;
Edit 1 (after question has been extended)
If you want to do that conditionally, you can do that with jsonb (because it supports the || operator)
select jsonb_build_object('name', p.name) || jsonb_strip_nulls(jsonb_build_object('birthday', p.birthday))
from person p;
Edit 2 (after Postgres version has been disclosed)
If you are limited to an old version of Postgres you need to use a conditional expression where you only concatenate the JSON object if the column is not null:
select jsonb_build_object('name', p.name)
|| case
when birthday is null then '{}'::jsonb
else jsonb_build_object('birthday', p.birthday)
end
from person p;
While the question is tagged postgresql-9.4, there is a new and better solution available since postgresql-16. It introduces JSON constructor functions like json_object.
If
ABSENT ON NULLis specified, the entire pair is omitted if the value_expression isNULL.
So you can use
SELECT json_object('name': p.name, 'birthday': p.birthday, ABSENT ON NULL)
FROM Person p
LIMIT 2;
For JSONB, there is no jsonb_object function but rather you use
json_object(… ABSENT ON NULL RETURNING jsonb)
json_object in the final PostgreSQL 15 documentation.