Skip to content

Commit 237a882

Browse files
adunstanadunstan
committed
Add json_strip_nulls and jsonb_strip_nulls functions.
The functions remove object fields, including in nested objects, that have null as a value. In certain cases this can lead to considerably smaller datums, with no loss of semantic information. Andrew Dunstan, reviewed by Pavel Stehule.
1 parent b1332e9 commit 237a882

File tree

11 files changed

+467
-1
lines changed

11 files changed

+467
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10716,6 +10716,19 @@ table2-mapping
1071610716
</programlisting>
1071710717
</entry>
1071810718
</row>
10719+
<row>
10720+
<entry><para><literal>json_strip_nulls(from_json json)</literal>
10721+
</para><para><literal>jsonb_strip_nulls(from_json jsonb)</literal>
10722+
</para></entry>
10723+
<entry><para><type>json</type></para><para><type>jsonb</type></para></entry>
10724+
<entry>
10725+
Returns <replaceable>from_json</replaceable>
10726+
with all object fields that have null values omitted. Other null values
10727+
are untouched.
10728+
</entry>
10729+
<entry><literal>json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
10730+
<entry><literal>[{"f1":1},2,null,3]</literal></entry>
10731+
</row>
1071910732
</tbody>
1072010733
</tgroup>
1072110734
</table>
@@ -10752,6 +10765,16 @@ table2-mapping
1075210765
</para>
1075310766
</note>
1075410767

10768+
<note>
10769+
<para>
10770+
If the argument to <literal>json_strip_nulls</> contains duplicate
10771+
field names in any object, the result could be semantically somewhat
10772+
different, depending on the order in which they occur. This is not an
10773+
issue for <literal>jsonb_strip_nulls</> since jsonb values never have
10774+
duplicate object field names.
10775+
</para>
10776+
</note>
10777+
1075510778
<para>
1075610779
See also <xref linkend="functions-aggregate"> for the aggregate
1075710780
function <function>json_agg</function> which aggregates record

‎src/backend/utils/adt/jsonfuncs.c

Lines changed: 197 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,15 @@ static void populate_recordset_object_end(void *state);
105105
static void populate_recordset_array_start(void *state);
106106
static void populate_recordset_array_element_start(void *state, bool isnull);
107107

108+
/* semantic action functions for json_strip_nulls */
109+
static void sn_object_start(void *state);
110+
static void sn_object_end(void *state);
111+
static void sn_array_start(void *state);
112+
static void sn_array_end(void *state);
113+
static void sn_object_field_start (void *state, char *fname, bool isnull);
114+
static void sn_array_element_start (void *state, bool isnull);
115+
static void sn_scalar(void *state, char *token, JsonTokenType tokentype);
116+
108117
/* worker function for populate_recordset and to_recordset */
109118
static Datum populate_recordset_worker(FunctionCallInfo fcinfo, const char *funcname,
110119
bool have_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
225234
MemoryContext fn_mcxt; /* used to stash IO funcs */
226235
} PopulateRecordsetState;
227236

237+
/* state for json_strip_nulls */
238+
typedef struct StripnullState{
239+
JsonLexContext *lex;
240+
StringInfo strval;
241+
bool skip_next_null;
242+
} StripnullState;
243+
228244
/* Turn a jsonb object into a record */
229245
static void make_row_from_rec_and_jsonb(Jsonb *element,
230246
PopulateRecordsetState *state);
@@ -2996,3 +3012,184 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
29963012

29973013
return findJsonbValueFromContainer(container, flags, &k);
29983014
}
3015+
3016+
/*
3017+
* Semantic actions for json_strip_nulls.
3018+
*
3019+
* Simply repeat the input on the output unless we encounter
3020+
* a null object field. State for this is set when the field
3021+
* is started and reset when the scalar action (which must be next)
3022+
* is called.
3023+
*/
3024+
3025+
static void
3026+
sn_object_start(void *state)
3027+
{
3028+
StripnullState *_state = (StripnullState *) state;
3029+
appendStringInfoCharMacro(_state->strval, '{');
3030+
}
3031+
3032+
static void
3033+
sn_object_end(void *state)
3034+
{
3035+
StripnullState *_state = (StripnullState *) state;
3036+
appendStringInfoCharMacro(_state->strval, '}');
3037+
}
3038+
3039+
static void
3040+
sn_array_start(void *state)
3041+
{
3042+
StripnullState *_state = (StripnullState *) state;
3043+
appendStringInfoCharMacro(_state->strval, '[');
3044+
}
3045+
3046+
static void
3047+
sn_array_end(void *state)
3048+
{
3049+
StripnullState *_state = (StripnullState *) state;
3050+
appendStringInfoCharMacro(_state->strval, ']');
3051+
}
3052+
3053+
static void
3054+
sn_object_field_start (void *state, char *fname, bool isnull)
3055+
{
3056+
StripnullState *_state = (StripnullState *) state;
3057+
3058+
if (isnull)
3059+
{
3060+
/*
3061+
* The next thing must be a scalar or isnull couldn't be true,
3062+
* so there is no danger of this state being carried down
3063+
* into a nested object or array. The flag will be reset in the
3064+
* scalar action.
3065+
*/
3066+
_state->skip_next_null = true;
3067+
return;
3068+
}
3069+
3070+
if (_state->strval->data[_state->strval->len - 1] != '{')
3071+
appendStringInfoCharMacro(_state->strval, ',');
3072+
3073+
/*
3074+
* Unfortunately we don't have the quoted and escaped string any more,
3075+
* so we have to re-escape it.
3076+
*/
3077+
escape_json(_state->strval,fname);
3078+
3079+
appendStringInfoCharMacro(_state->strval, ':');
3080+
}
3081+
3082+
static void
3083+
sn_array_element_start (void *state, bool isnull)
3084+
{
3085+
StripnullState *_state = (StripnullState *) state;
3086+
3087+
if (_state->strval->data[_state->strval->len - 1] != '[')
3088+
appendStringInfoCharMacro(_state->strval, ',');
3089+
}
3090+
3091+
static void
3092+
sn_scalar(void *state, char *token, JsonTokenType tokentype)
3093+
{
3094+
StripnullState *_state = (StripnullState *) state;
3095+
3096+
if (_state->skip_next_null)
3097+
{
3098+
Assert (tokentype == JSON_TOKEN_NULL);
3099+
_state->skip_next_null = false;
3100+
return;
3101+
}
3102+
3103+
if (tokentype == JSON_TOKEN_STRING)
3104+
escape_json(_state->strval, token);
3105+
else
3106+
appendStringInfoString(_state->strval, token);
3107+
}
3108+
3109+
/*
3110+
* SQL function json_strip_nulls(json) -> json
3111+
*/
3112+
Datum
3113+
json_strip_nulls(PG_FUNCTION_ARGS)
3114+
{
3115+
text *json = PG_GETARG_TEXT_P(0);
3116+
StripnullState *state;
3117+
JsonLexContext *lex;
3118+
JsonSemAction *sem;
3119+
3120+
lex = makeJsonLexContext(json, true);
3121+
state = palloc0(sizeof(StripnullState));
3122+
sem = palloc0(sizeof(JsonSemAction));
3123+
3124+
state->strval = makeStringInfo();
3125+
state->skip_next_null = false;
3126+
state->lex = lex;
3127+
3128+
sem->semstate = (void *) state;
3129+
sem->object_start = sn_object_start;
3130+
sem->object_end = sn_object_end;
3131+
sem->array_start = sn_array_start;
3132+
sem->array_end = sn_array_end;
3133+
sem->scalar = sn_scalar;
3134+
sem->array_element_start = sn_array_element_start;
3135+
sem->object_field_start = sn_object_field_start;
3136+
3137+
pg_parse_json(lex, sem);
3138+
3139+
PG_RETURN_TEXT_P(cstring_to_text_with_len(state->strval->data,
3140+
state->strval->len));
3141+
3142+
}
3143+
3144+
/*
3145+
* SQL function jsonb_strip_nulls(jsonb) -> jsonb
3146+
*/
3147+
Datum
3148+
jsonb_strip_nulls(PG_FUNCTION_ARGS)
3149+
{
3150+
Jsonb * jb = PG_GETARG_JSONB(0);
3151+
JsonbIterator *it;
3152+
JsonbParseState *parseState = NULL;
3153+
JsonbValue *res = NULL;
3154+
int type;
3155+
JsonbValue v,k;
3156+
bool last_was_key = false;
3157+
3158+
if (JB_ROOT_IS_SCALAR(jb))
3159+
PG_RETURN_POINTER(jb);
3160+
3161+
it = JsonbIteratorInit(&jb->root);
3162+
3163+
while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
3164+
{
3165+
Assert( ! (type == WJB_KEY && last_was_key));
3166+
3167+
if (type == WJB_KEY)
3168+
{
3169+
/* stash the key until we know if it has a null value */
3170+
k = v;
3171+
last_was_key = true;
3172+
continue;
3173+
}
3174+
3175+
if (last_was_key)
3176+
{
3177+
/* if the last element was a key this one can't be */
3178+
last_was_key = false;
3179+
3180+
/* skip this field if value is null */
3181+
if (type == WJB_VALUE && v.type == jbvNull)
3182+
continue;
3183+
3184+
/* otherwise, do a delayed push of the key */
3185+
res = pushJsonbValue(&parseState, WJB_KEY, &k);
3186+
}
3187+
3188+
if (type == WJB_VALUE || type == WJB_ELEM)
3189+
res = pushJsonbValue(&parseState, type, &v);
3190+
else
3191+
res = pushJsonbValue(&parseState, type, NULL);
3192+
}
3193+
3194+
PG_RETURN_POINTER(JsonbValueToJsonb(res));
3195+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201412022
56+
#define CATALOG_VERSION_NO 201412121
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4292,6 +4292,8 @@ DATA(insert OID = 3203 ( json_object PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0
42924292
DESCR("map text arrays of keys and values to json object");
42934293
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_ ));
42944294
DESCR("map input to json");
4295+
DATA(insert OID = 3261 ( json_strip_nulls PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 114 "114" _null_ _null_ _null_ _null_ json_strip_nulls _null_ _null_ _null_ ));
4296+
DESCR("remove object fields with null values from json");
42954297

42964298
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_ ));
42974299
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_ ));
@@ -4654,6 +4656,9 @@ DESCR("I/O");
46544656
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_ ));
46554657
DESCR("I/O");
46564658

4659+
DATA(insert OID = 3262 ( jsonb_strip_nulls PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 3802 "3802" _null_ _null_ _null_ _null_ jsonb_strip_nulls _null_ _null_ _null_ ));
4660+
DESCR("remove object fields with null values from jsonb");
4661+
46574662
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_ ));
46584663
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_ ));
46594664
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_ ));

‎src/include/utils/json.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@ extern Datum json_populate_record(PG_FUNCTION_ARGS);
6363
extern Datum json_populate_recordset(PG_FUNCTION_ARGS);
6464
extern Datum json_to_record(PG_FUNCTION_ARGS);
6565
extern Datum json_to_recordset(PG_FUNCTION_ARGS);
66+
extern Datum json_strip_nulls(PG_FUNCTION_ARGS);
6667

6768
extern Datum jsonb_object_field(PG_FUNCTION_ARGS);
6869
extern Datum jsonb_object_field_text(PG_FUNCTION_ARGS);
@@ -80,5 +81,6 @@ extern Datum jsonb_populate_record(PG_FUNCTION_ARGS);
8081
extern Datum jsonb_populate_recordset(PG_FUNCTION_ARGS);
8182
extern Datum jsonb_to_record(PG_FUNCTION_ARGS);
8283
extern Datum jsonb_to_recordset(PG_FUNCTION_ARGS);
84+
extern Datum jsonb_strip_nulls(PG_FUNCTION_ARGS);
8385

8486
#endif /* JSON_H */

‎src/test/regress/expected/json.out

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1586,3 +1586,53 @@ select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":fa
15861586
2 | {"d":"bar"} | f
15871587
(2 rows)
15881588

1589+
-- json_strip_nulls
1590+
select json_strip_nulls(null);
1591+
json_strip_nulls
1592+
------------------
1593+
1594+
(1 row)
1595+
1596+
select json_strip_nulls('1');
1597+
json_strip_nulls
1598+
------------------
1599+
1
1600+
(1 row)
1601+
1602+
select json_strip_nulls('"a string"');
1603+
json_strip_nulls
1604+
------------------
1605+
"a string"
1606+
(1 row)
1607+
1608+
select json_strip_nulls('null');
1609+
json_strip_nulls
1610+
------------------
1611+
null
1612+
(1 row)
1613+
1614+
select json_strip_nulls('[1,2,null,3,4]');
1615+
json_strip_nulls
1616+
------------------
1617+
[1,2,null,3,4]
1618+
(1 row)
1619+
1620+
select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}');
1621+
json_strip_nulls
1622+
------------------------------------
1623+
{"a":1,"c":[2,null,3],"d":{"e":4}}
1624+
(1 row)
1625+
1626+
select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
1627+
json_strip_nulls
1628+
---------------------
1629+
[1,{"a":1,"c":2},3]
1630+
(1 row)
1631+
1632+
-- an empty object is not null and should not be stripped
1633+
select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
1634+
json_strip_nulls
1635+
------------------
1636+
{"a":{},"d":{}}
1637+
(1 row)
1638+

0 commit comments

Comments
 (0)