Search code examples
arraysjsonpostgresqljsonb

PostgreSQL JSON building an array without null values

The following query

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

produces 

[{"use":"Home"},null]

When I actually want 

[{"use":"Home"}]

How do I go about doing this? json_strip_nulls() does not work for me.

Solution

  • By using a PostgreSQL array like that:

    SELECT array_to_json(array_remove(ARRAY[jsonb_build_object('use', 'Home'), 
        CASE WHEN 1 = 2 THEN jsonb_build_object('use', 'Work') END], null))

    which does produce:

    [{"use": "Home"}]

    while, to be sure:

    SELECT array_to_json(array_remove(ARRAY[jsonb_build_object('use', 'Home'), 
        CASE WHEN 1 = 2 THEN jsonb_build_object('use', 'Work') END,
        jsonb_build_object('real_use', 'NotHome')], null))

    does produce:

    [{"use": "Home"},{"real_use": "NotHome"}]