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,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 256ef80..1ecc17a 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -60,6 +60,13 @@ typedef struct JsonbAggState
Oid val_output_func;
} JsonbAggState;
+typedef enum /* type categories for jsonb to string whitespace */
+{
+ JSONBWHITESPACE_DEFAULT, /* Default style with space between keys but no indentation */
+ JSONBWHITESPACE_INDENT, /* Extra indentation of four spaces for each nested level */
+ JSONBWHITESPACE_COMPACT /* Compact style without extra whitespace between keys */
+} JsonbWhitespaceStyle;
+
static inline Datum jsonb_from_cstring(char *json, int len);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
@@ -86,7 +93,7 @@ static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
Oid val_type, bool key_scalar);
static JsonbParseState *clone_parse_state(JsonbParseState *state);
-static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent);
+static char *JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, JsonbWhitespaceStyle whitespace_style);
static void add_indent(StringInfo out, bool indent, int level);
/*
@@ -427,7 +434,7 @@ jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
char *
JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
{
- return JsonbToCStringWorker(out, in, estimated_len, false);
+ return JsonbToCStringWorker(out, in, estimated_len, JSONBWHITESPACE_DEFAULT);
}
/*
@@ -436,14 +443,23 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
char *
JsonbToCStringIndent(StringInfo out, JsonbContainer *in, int estimated_len)
{
- return JsonbToCStringWorker(out, in, estimated_len, true);
+ return JsonbToCStringWorker(out, in, estimated_len, JSONBWHITESPACE_INDENT);
+}
+
+/*
+ * same thing but in compact form (no extra whitespace)
+ */
+char *
+JsonbToCStringCompact(StringInfo out, JsonbContainer *in, int estimated_len)
+{
+ return JsonbToCStringWorker(out, in, estimated_len, JSONBWHITESPACE_COMPACT);
}
/*
* common worker for above two functions
*/
static char *
-JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool indent)
+JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, JsonbWhitespaceStyle whitespace_style)
{
bool first = true;
JsonbIterator *it;
@@ -452,8 +468,24 @@ JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool
int level = 0;
bool redo_switch = false;
- /* If we are indenting, don't add a space after a comma */
- int ispaces = indent ? 1 : 2;
+ bool indent = false;
+ char* prop_sep = ", "; /* Separator between successive properties */
+ char* key_value_sep = ": "; /* Separator between a key and it's value */
+ int prop_sep_len;
+ int key_value_sep_len;
+ if (whitespace_style == JSONBWHITESPACE_DEFAULT) {
+ // Use default separators
+ } else if (whitespace_style == JSONBWHITESPACE_INDENT) {
+ indent = true;
+ prop_sep = ",";
+ key_value_sep = ": ";
+ } else if (whitespace_style == JSONBWHITESPACE_COMPACT) {
+ indent = false;
+ prop_sep = ",";
+ key_value_sep = ":";
+ }
+ prop_sep_len = strlen(prop_sep);
+ key_value_sep_len = strlen(key_value_sep);
/*
* Don't indent the very first item. This gets set to the indent flag at
@@ -478,7 +510,7 @@ JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool
{
case WJB_BEGIN_ARRAY:
if (!first)
- appendBinaryStringInfo(out, ", ", ispaces);
+ appendBinaryStringInfo(out, prop_sep, prop_sep_len);
if (!v.val.array.rawScalar)
{
@@ -493,7 +525,7 @@ JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool
break;
case WJB_BEGIN_OBJECT:
if (!first)
- appendBinaryStringInfo(out, ", ", ispaces);
+ appendBinaryStringInfo(out, prop_sep, prop_sep_len);
add_indent(out, use_indent && !last_was_key, level);
appendStringInfoCharMacro(out, '{');
@@ -503,14 +535,14 @@ JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool
break;
case WJB_KEY:
if (!first)
- appendBinaryStringInfo(out, ", ", ispaces);
+ appendBinaryStringInfo(out, prop_sep, prop_sep_len);
first = true;
add_indent(out, use_indent, level);
/* json rules guarantee this is a string */
jsonb_put_escaped_value(out, &v);
- appendBinaryStringInfo(out, ": ", 2);
+ appendBinaryStringInfo(out, key_value_sep, key_value_sep_len);
type = JsonbIteratorNext(&it, &v, false);
if (type == WJB_VALUE)
@@ -532,7 +564,7 @@ JsonbToCStringWorker(StringInfo out, JsonbContainer *in, int estimated_len, bool
break;
case WJB_ELEM:
if (!first)
- appendBinaryStringInfo(out, ", ", ispaces);
+ appendBinaryStringInfo(out, prop_sep, prop_sep_len);
first = false;
if (!raw_scalar)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index fb149dc..0ab324b 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -3353,6 +3353,22 @@ jsonb_pretty(PG_FUNCTION_ARGS)
}
/*
+ * SQL function jsonb_compact (jsonb)
+ *
+ * Compact-printed text for the jsonb
+ */
+Datum
+jsonb_compact(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB(0);
+ StringInfo str = makeStringInfo();
+
+ JsonbToCStringCompact(str, &jb->root, VARSIZE(jb));
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(str->data, str->len));
+}
+
+/*
* SQL function jsonb_concat (jsonb, jsonb)
*
* function for || operator
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bb539d4..0033071 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4880,6 +4880,8 @@ DATA(insert OID = 3305 ( jsonb_set PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4
DESCR("Set part of a jsonb");
DATA(insert OID = 3306 ( jsonb_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _null_ _null_ _null_ ));
DESCR("Indented text from jsonb");
+DATA(insert OID = 3369 ( jsonb_compact PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_compact _null_ _null_ _null_ ));
+DESCR("Compact text from jsonb");
DATA(insert OID = 3579 ( jsonb_insert PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4 0 3802 "3802 1009 3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_insert _null_ _null_ _null_ ));
DESCR("Insert value into a jsonb");
/* txid */
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 5d8e4a9..4ce4fab 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -397,6 +397,9 @@ extern Datum gin_triconsistent_jsonb_path(PG_FUNCTION_ARGS);
/* pretty printer, returns text */
extern Datum jsonb_pretty(PG_FUNCTION_ARGS);
+/* compact printer, returns text */
+extern Datum jsonb_compact(PG_FUNCTION_ARGS);
+
/* concatenation */
extern Datum jsonb_concat(PG_FUNCTION_ARGS);
@@ -435,6 +438,8 @@ extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
int estimated_len);
extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
int estimated_len);
+extern char *JsonbToCStringCompact(StringInfo out, JsonbContainer *in,
+ int estimated_len);
#endif /* __JSONB_H__ */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
