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;