Hi,
The default text representation of jsonb adds whitespace in between key/value pairs (after the colon ":") and after successive properties (after the comma ","):
postgres=# SELECT '{"b":2,"a":1}'::jsonb::text;
text
------------------
{"a": 1, "b": 2}
(1 row)
AFAIK, there's also no guarantee on the specific order of the resulting properties in the text representation either. I would suppose it's fixed for a given jsonb value within a database major version but across major versions it could change (if the underlying representation changes).
I originally ran into this when comparing the hashes of the text representation of jsonb columns. The results didn't match up because the javascript function JSON.stringify(...) does not add any extra whitespace.
It'd be nice to have a stable text representation of a jsonb value with minimal whitespace. The latter would also save a few bytes per record in text output formats, on the wire, and in backups (ex: COPY ... TO STDOUT).
Attached is a *very* work in progress patch that adds a jsonb_compact(jsonb)::text function. It generates a text representation without extra whitespace but does not yet try to enforce a stable order of the properties within a jsonb value.
Here's some sample usage:
postgres=# SELECT x::text, jsonb_compact(x) FROM (VALUES ('{}'::jsonb), ('{"a":1,"b":2}'), ('[1,2,3]'), ('{"a":{"b":1}}')) AS t(x);
x | jsonb_compact
------------------+---------------
{} | {}
{"a": 1, "b": 2} | {"a":1,"b":2}
[1, 2, 3] | [1,2,3]
{"a": {"b": 1}} | {"a":{"b":1}}
(4 rows)
There aren't any tests yet but I'd like to continue working on it for inclusion in 9.7. I'm posting it now to see if there's interest in the idea and get some feedback on the approach (this is my first real patch...).
Regards,