Postgres json_object. Add json field only if value is not null

Question

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?

Answer 1

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;