PostgreSQL JSON building an array without null values

Creating a custom function seems to be the simplest way.

create or replace function jsonb_build_array_without_nulls(variadic anyarray)
returns jsonb language sql immutable as $$
    select jsonb_agg(elem)
    from unnest($1) as elem
    where elem is not null
$$;

select 
    jsonb_build_array_without_nulls(
        jsonb_build_object('use', 'home'), 
        case when 1 = 2 then jsonb_build_object('use', 'work') end
    )

 jsonb_build_array_without_nulls 
---------------------------------
 [{"use": "home"}]
(1 row) 

One other way that this can be handled is the following:

SELECT jsonb_build_array(
  jsonb_build_object('use', 'Home'), 
  CASE 
    WHEN 1 = 2 THEN jsonb_build_object('use', 'Work')
    ELSE '"null"'
  END
) - 'null'

(Unfortunately it's not really possible to do much with null by itself in postgres -- or most other DBs)

In the case above, '"null"' could be replaced with just about any unique string that would not be mistaken for live data in the array. I would not use numbers since - 0 would actually try to remove the first item from the array, rather than a number within the array. But you could probably use '"0"' and remove using something like - '0' if you wanted.

For those not using CASE, a COALESCE could be used to convert nulls into the desired string (alas, no NVL, IFNULL or ISNULL in postgres, but at least COALESCE is portable)

Postgres 16 did introduce JSON constructor functions like json_array.

If ABSENT ON NULL is specified, NULL values are ignored

So you can use

SELECT json_array(
    json_object('use': 'Home'), 
    (CASE WHEN 1 = 2 THEN json_object('use': 'Work') END),
    ABSENT ON NULL
)

For JSONB, there is no jsonb_array function but rather you use

json_array(… ABSENT ON NULL RETURNING jsonb)

I'm assuming this query is generaetd dynamically, somehow. If you're in control of the SQL generation, you can also use ARRAY_AGG(...) FILTER(...) instead, which, depending on your real world query, might be more convenient than using all the different array conversion functions suggested by Patrick.

SELECT (
  SELECT json_agg(v) FILTER (WHERE v IS NOT NULL)
  FROM (
    VALUES 
      (jsonb_build_object('use', 'Home')),
      (CASE WHEN 1 = 2 THEN jsonb_build_object('use', 'Work') END)
  ) t (v)
)

Or also:

SELECT (
  SELECT json_agg(v)
  FROM (
    VALUES 
      (jsonb_build_object('use', 'Home')),
      (CASE WHEN 1 = 2 THEN jsonb_build_object('use', 'Work') END)
  ) t (v)
  WHERE v IS NOT NULL
)