fetching compact version of JSONB in PostgreSQL

Solution 1:

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.

Solution 2:

jsonb is rendered in a standardized format on output. You would have to use json instead to preserve insignificant white space. Per documentation:

Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys.

The whitespace really shouldn't matter for JSON values.

Solution 3:

There is a discussion, started in 2016, about a function jsonb_compact() that will solve the problem... But, it could take years (!).

Pretty solution

(a real solution for this question and this other one)

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"}

Rationale

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 datatype to text has no canonical form. The PostgreSQL cast convention (using spaces) is arbitrary.
A lot of applications need to minimize a big JSONb output. Two typical ones: minimizing file size of a big JSONb saved by pg_file_write(); output online in a REST interface.
The PostgreSQL team must to appreciate a real CAST procedure, not a parser, but a direct text production from JSONb internal representation.
- The workaround — to remove spaces from "JSON text" — is not a simple task, it need a good parser to avoid tampering content. The solution is a parser, it is not a regular expression workaround... And in nowadays the built-in parser is json_strip_nulls(), even as "incidential behavior" parser.