patternsqlMinor

Equivalent to json_strip_nulls for Postgres 9.4

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

forjson_strip_nullsequivalentpostgres

Problem

The json_strip_nulls function was introduced in 9.5. What could give an equivalent result for 9.4?

I have a json column that is essentially just a text column, but since the contents are JSON, we made it a json column for semantic purposes. Now there are a lot of keys mapped to null, and it's wasting space and causing clutter on the screen.

Casting to jsonb and back doesn't remove the nulls, and 9.4 doesn't give a lot of tools to work with json(b).

Solution

I think this can be done using an custom aggregate and existing jsonb functionality:

The following aggregate will "merge" two jsonb values into one. Essentially the same as a || b (where a and b are jsonb values) but as an aggregate for multiple rows.

create aggregate jsonb_merge(jsonb) 
(
    sfunc = jsonb_concat(jsonb, jsonb),
    stype = jsonb
);

With that aggregate it's possible to write a recursive function that removes keys with null values:

create function strip_nulls(p_input jsonb)
  returns jsonb
as
$
  select jsonb_aggregate(o)
  from (
    select jsonb_build_object(t.k, case jsonb_typeof(t.v) when 'object' then strip_nulls(t.v) else t.v end) o
    from jsonb_each(p_input) as t(k,v)
    where t.v::text <> 'null'
  ) t;
$
language sql;

The inner select turns a jsonb value into a set of key/value pairs and removes the null values recursively. Then the resulting values are aggregated back into a jsonb object.

select strip_nulls('{"one": null, 
                     "two": 2, 
                     "three": {"four": 1, "five": null, 
                               "six": {"seven": 7, "eight": null}
                              }
                    }'::jsonb)

returns:

strip_nulls                                          
-----------------------------------------------------
{"two": 2, "three": {"six": {"seven": 7}, "four": 1}}

The result can be cast back to json

I don't have a Postgres 9.4 installation around for testing, but I think I only used functions available in 9.4.

Code Snippets

create aggregate jsonb_merge(jsonb) 
(
    sfunc = jsonb_concat(jsonb, jsonb),
    stype = jsonb
);

create function strip_nulls(p_input jsonb)
  returns jsonb
as
$$
  select jsonb_aggregate(o)
  from (
    select jsonb_build_object(t.k, case jsonb_typeof(t.v) when 'object' then strip_nulls(t.v) else t.v end) o
    from jsonb_each(p_input) as t(k,v)
    where t.v::text <> 'null'
  ) t;
$$
language sql;

select strip_nulls('{"one": null, 
                     "two": 2, 
                     "three": {"four": 1, "five": null, 
                               "six": {"seven": 7, "eight": null}
                              }
                    }'::jsonb)

strip_nulls                                          
-----------------------------------------------------
{"two": 2, "three": {"six": {"seven": 7}, "four": 1}}

Context

StackExchange Database Administrators Q#197256, answer score: 2

Revisions (0)

No revisions yet.