Search code examples
stringpostgresqljsonb

fetching compact version of JSONB in PostgreSQL

How to fetch compact JSONB from PostgreSQL?

All I got when fetching is with spaces:

 SELECT data FROM a_table WHERE id = 1; -- data is JSONB column

 {"unique": "bla bla", "foo": {"bar": {"in ...
^          ^          ^      ^       ^ --> spaces

What I want is:

{"unique":"bla bla","foo":{"bar":{"in ...
Solution

  • json_strip_nulls() does exactly what you're looking for:

    SELECT json_build_object('a', 1);

    returns

    {"a" : 1}

    But

    SELECT json_strip_nulls(json_build_object('a', 1));

    returns

    {"a":1}

    This function not only strips nulls as indicated by its function name and as documented, but incidentally also strips insignificant whitespace. The latter is not explicitly documented in PostgreSQL manual.

    Tested in PostgreSQL 11.3, but probably works with earlier versions too.