Convert JSONB to minified (no spaces) String

Question

If I convert a text value like {"a":"b"} to JSONB and then back to text a space () is added between the : and the ".

psql=> select '{"a":"b"}'::jsonb::text;
    text    
------------
 {"a": "b"}
(1 row)

How can I convert a text to a jsonb, so I can use jsonb functions, and back to text to store it?

Answer 1

The JSON standard, RFC 8259, says "... Insignificant whitespace is allowed before or after any of the six structural characters". In other words, the cast from jsonb to text has no universal canonical form. The PostgreSQL cast convention (using spaces) is arbitrary.

So, we must to agree with the PostgreSQL's convention for CAST(var_jsonb AS text). When you need another cast convention, for example to debug or human-readable output, the built-in jsonb_pretty() function is a good choice.

Unfortunately PostgreSQL not offers other choices, like the compact one. So, you can overload jsonb_pretty() with a compact option:

CREATE or replace FUNCTION jsonb_pretty(
  jsonb,            -- input
  compact boolean   -- true for compact format
) RETURNS text AS $$
  SELECT CASE
    WHEN $2=true THEN json_strip_nulls($1::json)::text
    ELSE  jsonb_pretty($1)
  END
$$ LANGUAGE SQL IMMUTABLE;

SELECT jsonb_pretty(  jsonb_build_object('a',1, 'bla','bla bla'), true );
-- results  {"a":1,"bla":"bla bla"}

See a complete discussion at this similar question.