As discussed recently, here is an undocumented patch for
json_strip_nulls and jsonb_strip_nulls.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 2d00dbe..e9636d8 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -105,6 +105,15 @@ static void populate_recordset_object_end(void *state);
static void populate_recordset_array_start(void *state);
static void populate_recordset_array_element_start(void *state, bool isnull);
+/* semantic action functions for json_strip_nulls */
+static void sn_object_start(void *state);
+static void sn_object_end(void *state);
+static void sn_array_start(void *state);
+static void sn_array_end(void *state);
+static void sn_object_field_start (void *state, char *fname, bool isnull);
+static void sn_array_element_start (void *state, bool isnull);
+static void sn_scalar(void *state, char *token, JsonTokenType tokentype);
+
/* worker function for populate_recordset and to_recordset */
static Datum populate_recordset_worker(FunctionCallInfo fcinfo, const char *funcname,
bool have_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
MemoryContext fn_mcxt; /* used to stash IO funcs */
} PopulateRecordsetState;
+/* state for json_strip_nulls */
+typedef struct StripnullState{
+ JsonLexContext *lex;
+ StringInfo strval;
+ bool skip_next_null;
+} StripnullState;
+
/* Turn a jsonb object into a record */
static void make_row_from_rec_and_jsonb(Jsonb *element,
PopulateRecordsetState *state);
@@ -2996,3 +3012,169 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
return findJsonbValueFromContainer(container, flags, &k);
}
+
+static void
+sn_object_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state->strval, '{');
+}
+
+static void
+sn_object_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state->strval, '}');
+}
+
+static void
+sn_array_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state->strval, '[');
+}
+
+static void
+sn_array_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state->strval, ']');
+}
+
+static void
+sn_object_field_start (void *state, char *fname, bool isnull)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (isnull)
+ {
+ _state->skip_next_null = true;
+ return;
+ }
+
+ if (_state->strval->data[_state->strval->len - 1] != '{')
+ appendStringInfoCharMacro(_state->strval, ',');
+
+ /*
+ * Unfortunately we don't have the quoted and escaped string any more,
+ * so we have to re-escape it.
+ */
+ escape_json(_state->strval,fname);
+
+ appendStringInfoCharMacro(_state->strval, ':');
+}
+
+static void
+sn_array_element_start (void *state, bool isnull)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (_state->strval->data[_state->strval->len - 1] != '[')
+ appendStringInfoCharMacro(_state->strval, ',');
+}
+
+static void
+sn_scalar(void *state, char *token, JsonTokenType tokentype)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (_state->skip_next_null)
+ {
+ _state->skip_next_null = false;
+ return;
+ }
+
+ if (tokentype == JSON_TOKEN_STRING)
+ escape_json(_state->strval, token);
+ else
+ appendStringInfoString(_state->strval, token);
+}
+
+/*
+ * SQL function json_strip_nulls(json) -> json
+ */
+Datum
+json_strip_nulls(PG_FUNCTION_ARGS)
+{
+ text *json = PG_GETARG_TEXT_P(0);
+ StripnullState *state;
+ JsonLexContext *lex;
+ JsonSemAction *sem;
+
+ lex = makeJsonLexContext(json, true);
+ state = palloc0(sizeof(StripnullState));
+ sem = palloc0(sizeof(JsonSemAction));
+
+ state->strval = makeStringInfo();
+ state->skip_next_null = false;
+ state->lex = lex;
+
+ sem->semstate = (void *) state;
+ sem->object_start = sn_object_start;
+ sem->object_end = sn_object_end;
+ sem->array_start = sn_array_start;
+ sem->array_end = sn_array_end;
+ sem->scalar = sn_scalar;
+ sem->array_element_start = sn_array_element_start;
+ sem->object_field_start = sn_object_field_start;
+
+ pg_parse_json(lex, sem);
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(state->strval->data,
+ state->strval->len));
+
+}
+
+/*
+ * SQL function jsonb_strip_nulls(jsonb) -> jsonb
+ */
+Datum
+jsonb_strip_nulls(PG_FUNCTION_ARGS)
+{
+ Jsonb * jb = PG_GETARG_JSONB(0);
+ JsonbIterator *it;
+ JsonbParseState *parseState = NULL;
+ JsonbValue *res;
+ int type;
+ JsonbValue v,k;
+ bool last_was_key = false;
+
+ if (JB_ROOT_IS_SCALAR(jb))
+ PG_RETURN_POINTER(jb);
+
+ it = JsonbIteratorInit(&jb->root);
+
+ while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
+ {
+ Assert( ! (type == WJB_KEY && last_was_key));
+
+ if (type == WJB_KEY)
+ {
+ /* stash the key until we know if it has a null value */
+ k = v;
+ last_was_key = true;
+ continue;
+ }
+
+ if (last_was_key)
+ {
+ /* skip this field if null */
+ if (type == WJB_VALUE && v.type == jbvNull)
+ {
+ last_was_key = false;
+ continue;
+ }
+
+ /* otherwise, do a delayed push of the key */
+ res = pushJsonbValue(&parseState, WJB_KEY, &k);
+ }
+
+ if (type == WJB_VALUE || type == WJB_ELEM)
+ res = pushJsonbValue(&parseState, type, &v);
+ else
+ res = pushJsonbValue(&parseState, type, NULL);
+ last_was_key = false;
+ }
+
+ PG_RETURN_POINTER(JsonbValueToJsonb(res));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3ce9849..85a79d0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4233,6 +4233,8 @@ DATA(insert OID = 3203 ( json_object PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0
DESCR("map text arrays of keys and values to json object");
DATA(insert OID = 3176 ( to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 114 "2283" _null_ _null_ _null_ _null_ to_json _null_ _null_ _null_ ));
DESCR("map input to json");
+DATA(insert OID = 3300 ( json_strip_nulls PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 114 "114" _null_ _null_ _null_ _null_ json_strip_nulls _null_ _null_ _null_ ));
+DESCR("remove object fields with null values from json");
DATA(insert OID = 3947 ( json_object_field PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 114 "114 25" _null_ _null_ "{from_json, field_name}" _null_ json_object_field _null_ _null_ _null_ ));
DATA(insert OID = 3948 ( json_object_field_text PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 25 "114 25" _null_ _null_ "{from_json, field_name}" _null_ json_object_field_text _null_ _null_ _null_ ));
@@ -4595,6 +4597,9 @@ DESCR("I/O");
DATA(insert OID = 3803 ( jsonb_send PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 17 "3802" _null_ _null_ _null_ _null_ jsonb_send _null_ _null_ _null_ ));
DESCR("I/O");
+DATA(insert OID = 3301 ( jsonb_strip_nulls PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3802 "3802" _null_ _null_ _null_ _null_ jsonb_strip_nulls _null_ _null_ _null_ ));
+DESCR("remove object fields with null values from jsonb");
+
DATA(insert OID = 3478 ( jsonb_object_field PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 25" _null_ _null_ "{from_json, field_name}" _null_ jsonb_object_field _null_ _null_ _null_ ));
DATA(insert OID = 3214 ( jsonb_object_field_text PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 25 "3802 25" _null_ _null_ "{from_json, field_name}" _null_ jsonb_object_field_text _null_ _null_ _null_ ));
DATA(insert OID = 3215 ( jsonb_array_element PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 23" _null_ _null_ "{from_json, element_index}" _null_ jsonb_array_element _null_ _null_ _null_ ));
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index 9d8a0a5..ea819f3 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -61,6 +61,7 @@ extern Datum json_populate_record(PG_FUNCTION_ARGS);
extern Datum json_populate_recordset(PG_FUNCTION_ARGS);
extern Datum json_to_record(PG_FUNCTION_ARGS);
extern Datum json_to_recordset(PG_FUNCTION_ARGS);
+extern Datum json_strip_nulls(PG_FUNCTION_ARGS);
extern Datum jsonb_object_field(PG_FUNCTION_ARGS);
extern Datum jsonb_object_field_text(PG_FUNCTION_ARGS);
@@ -78,5 +79,6 @@ extern Datum jsonb_populate_record(PG_FUNCTION_ARGS);
extern Datum jsonb_populate_recordset(PG_FUNCTION_ARGS);
extern Datum jsonb_to_record(PG_FUNCTION_ARGS);
extern Datum jsonb_to_recordset(PG_FUNCTION_ARGS);
+extern Datum jsonb_strip_nulls(PG_FUNCTION_ARGS);
#endif /* JSON_H */
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 968e02a..efc4c9c 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -1644,3 +1644,46 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa
2 | {"d":"bar"} | f
(2 rows)
+-- json_strip_nulls
+select json_strip_nulls(null);
+ json_strip_nulls
+------------------
+
+(1 row)
+
+select json_strip_nulls('1');
+ json_strip_nulls
+------------------
+ 1
+(1 row)
+
+select json_strip_nulls('"a string"');
+ json_strip_nulls
+------------------
+ "a string"
+(1 row)
+
+select json_strip_nulls('null');
+ json_strip_nulls
+------------------
+ null
+(1 row)
+
+select json_strip_nulls('[1,2,null,3,4]');
+ json_strip_nulls
+------------------
+ [1,2,null,3,4]
+(1 row)
+
+select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+ json_strip_nulls
+------------------------------------
+ {"a":1,"c":[2,null,3],"d":{"e":4}}
+(1 row)
+
+select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
+ json_strip_nulls
+---------------------
+ [1,{"a":1,"c":2},3]
+(1 row)
+
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index f3baf32..7976a93 100644
--- a/src/test/regress/expected/json_1.out
+++ b/src/test/regress/expected/json_1.out
@@ -1640,3 +1640,46 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa
2 | {"d":"bar"} | f
(2 rows)
+-- json_strip_nulls
+select json_strip_nulls(null);
+ json_strip_nulls
+------------------
+
+(1 row)
+
+select json_strip_nulls('1');
+ json_strip_nulls
+------------------
+ 1
+(1 row)
+
+select json_strip_nulls('"a string"');
+ json_strip_nulls
+------------------
+ "a string"
+(1 row)
+
+select json_strip_nulls('null');
+ json_strip_nulls
+------------------
+ null
+(1 row)
+
+select json_strip_nulls('[1,2,null,3,4]');
+ json_strip_nulls
+------------------
+ [1,2,null,3,4]
+(1 row)
+
+select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+ json_strip_nulls
+------------------------------------
+ {"a":1,"c":[2,null,3],"d":{"e":4}}
+(1 row)
+
+select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
+ json_strip_nulls
+---------------------
+ [1,{"a":1,"c":2},3]
+(1 row)
+
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index eb37da7..7e7c6b9 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -2436,3 +2436,46 @@ SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e';
f
(1 row)
+-- jsonb_strip_nulls
+select jsonb_strip_nulls(null);
+ jsonb_strip_nulls
+-------------------
+
+(1 row)
+
+select jsonb_strip_nulls('1');
+ jsonb_strip_nulls
+-------------------
+ 1
+(1 row)
+
+select jsonb_strip_nulls('"a string"');
+ jsonb_strip_nulls
+-------------------
+ "a string"
+(1 row)
+
+select jsonb_strip_nulls('null');
+ jsonb_strip_nulls
+-------------------
+ null
+(1 row)
+
+select jsonb_strip_nulls('[1,2,null,3,4]');
+ jsonb_strip_nulls
+--------------------
+ [1, 2, null, 3, 4]
+(1 row)
+
+select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+ jsonb_strip_nulls
+--------------------------------------------
+ {"a": 1, "c": [2, null, 3], "d": {"e": 4}}
+(1 row)
+
+select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
+ jsonb_strip_nulls
+--------------------------
+ [1, {"a": 1, "c": 2}, 3]
+(1 row)
+
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index f3bfc7b..eef9988 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -2436,3 +2436,46 @@ SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e';
f
(1 row)
+-- jsonb_strip_nulls
+select jsonb_strip_nulls(null);
+ jsonb_strip_nulls
+-------------------
+
+(1 row)
+
+select jsonb_strip_nulls('1');
+ jsonb_strip_nulls
+-------------------
+ 1
+(1 row)
+
+select jsonb_strip_nulls('"a string"');
+ jsonb_strip_nulls
+-------------------
+ "a string"
+(1 row)
+
+select jsonb_strip_nulls('null');
+ jsonb_strip_nulls
+-------------------
+ null
+(1 row)
+
+select jsonb_strip_nulls('[1,2,null,3,4]');
+ jsonb_strip_nulls
+--------------------
+ [1, 2, null, 3, 4]
+(1 row)
+
+select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+ jsonb_strip_nulls
+--------------------------------------------
+ {"a": 1, "c": [2, null, 3], "d": {"e": 4}}
+(1 row)
+
+select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
+ jsonb_strip_nulls
+--------------------------
+ [1, {"a": 1, "c": 2}, 3]
+(1 row)
+
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 3ddf656..41924c4 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -533,3 +533,20 @@ select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","
select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]')
as x(a int, b json, c boolean);
+
+
+-- json_strip_nulls
+
+select json_strip_nulls(null);
+
+select json_strip_nulls('1');
+
+select json_strip_nulls('"a string"');
+
+select json_strip_nulls('null');
+
+select json_strip_nulls('[1,2,null,3,4]');
+
+select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+
+select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index ed266d5..edb2c00 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -535,3 +535,19 @@ SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'b';
SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'c';
SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'd';
SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::jsonb ? 'e';
+
+-- jsonb_strip_nulls
+
+select jsonb_strip_nulls(null);
+
+select jsonb_strip_nulls('1');
+
+select jsonb_strip_nulls('"a string"');
+
+select jsonb_strip_nulls('null');
+
+select jsonb_strip_nulls('[1,2,null,3,4]');
+
+select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
+
+select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
