Equivalent to json_strip_nulls for Postgres 9.4

Question

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).

• user1822: If that is so important, why not simply upgrade to 9.6 or 10?
• user1822: Do you only have top-level keys or do you need that recursively as well? e.g. for {"k1": 1, "k2": null, "k3": {"k31": 31, "k32": null}}
• Kayaman: Well it's not that important, and an upgrade is long due. I was just wondering if there might be a simple workaround that could be used for the time being, I'm not trying to go above and beyond just for this. Optimal solution would handle recursively, although the max depth of the JSON is 3-4.

Answer 1

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.

• Kayaman: Seems like jsonb_concat isn't available in 9.4. I suppose the big feature in 9.4 was introducing jsonb` in addition to json, and improved tooling only came with 9.5 onwards.
• user1822: @Kayaman: ah, right. See this question for a function to mimic the jsonb_concat in 9.4
• Kayaman: Thanks! With the old json_merge and replacing some jsonb functions with their json counterparts and casts (jsonb_object_agg, jsonb_build_object etc.) I managed to get it working. It doesn't seem to handle arrays, but otherwise seems to work.