Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit237a882

Browse files
committed
Add json_strip_nulls and jsonb_strip_nulls functions.
The functions remove object fields, including in nested objects, thathave null as a value. In certain cases this can lead to considerablysmaller datums, with no loss of semantic information.Andrew Dunstan, reviewed by Pavel Stehule.
1 parentb1332e9 commit237a882

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
staticvoidpopulate_recordset_array_start(void*state);
106106
staticvoidpopulate_recordset_array_element_start(void*state,boolisnull);
107107

108+
/* semantic action functions for json_strip_nulls */
109+
staticvoidsn_object_start(void*state);
110+
staticvoidsn_object_end(void*state);
111+
staticvoidsn_array_start(void*state);
112+
staticvoidsn_array_end(void*state);
113+
staticvoidsn_object_field_start (void*state,char*fname,boolisnull);
114+
staticvoidsn_array_element_start (void*state,boolisnull);
115+
staticvoidsn_scalar(void*state,char*token,JsonTokenTypetokentype);
116+
108117
/* worker function for populate_recordset and to_recordset */
109118
staticDatumpopulate_recordset_worker(FunctionCallInfofcinfo,constchar*funcname,
110119
boolhave_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
225234
MemoryContextfn_mcxt;/* used to stash IO funcs */
226235
}PopulateRecordsetState;
227236

237+
/* state for json_strip_nulls */
238+
typedefstructStripnullState{
239+
JsonLexContext*lex;
240+
StringInfostrval;
241+
boolskip_next_null;
242+
}StripnullState;
243+
228244
/* Turn a jsonb object into a record */
229245
staticvoidmake_row_from_rec_and_jsonb(Jsonb*element,
230246
PopulateRecordsetState*state);
@@ -2996,3 +3012,184 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
29963012

29973013
returnfindJsonbValueFromContainer(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+
staticvoid
3026+
sn_object_start(void*state)
3027+
{
3028+
StripnullState*_state= (StripnullState*)state;
3029+
appendStringInfoCharMacro(_state->strval,'{');
3030+
}
3031+
3032+
staticvoid
3033+
sn_object_end(void*state)
3034+
{
3035+
StripnullState*_state= (StripnullState*)state;
3036+
appendStringInfoCharMacro(_state->strval,'}');
3037+
}
3038+
3039+
staticvoid
3040+
sn_array_start(void*state)
3041+
{
3042+
StripnullState*_state= (StripnullState*)state;
3043+
appendStringInfoCharMacro(_state->strval,'[');
3044+
}
3045+
3046+
staticvoid
3047+
sn_array_end(void*state)
3048+
{
3049+
StripnullState*_state= (StripnullState*)state;
3050+
appendStringInfoCharMacro(_state->strval,']');
3051+
}
3052+
3053+
staticvoid
3054+
sn_object_field_start (void*state,char*fname,boolisnull)
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+
staticvoid
3083+
sn_array_element_start (void*state,boolisnull)
3084+
{
3085+
StripnullState*_state= (StripnullState*)state;
3086+
3087+
if (_state->strval->data[_state->strval->len-1]!='[')
3088+
appendStringInfoCharMacro(_state->strval,',');
3089+
}
3090+
3091+
staticvoid
3092+
sn_scalar(void*state,char*token,JsonTokenTypetokentype)
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+
inttype;
3155+
JsonbValuev,k;
3156+
boollast_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-
#defineCATALOG_VERSION_NO201412022
56+
#defineCATALOG_VERSION_NO201412121
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(insertOID=3176 (to_jsonPGNSPPGUID121000fffftfs10114"2283"_null__null__null__null_to_json_null__null__null_ ));
42944294
DESCR("map input to json");
4295+
DATA(insertOID=3261 (json_strip_nullsPGNSPPGUID121000fffftfi10114"114"_null__null__null__null_json_strip_nulls_null__null__null_ ));
4296+
DESCR("remove object fields with null values from json");
42954297

42964298
DATA(insertOID=3947 (json_object_fieldPGNSPPGUID121000fffftfi20114"114 25"_null__null_"{from_json, field_name}"_null_json_object_field_null__null__null_ ));
42974299
DATA(insertOID=3948 (json_object_field_textPGNSPPGUID121000fffftfi2025"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(insertOID=3803 (jsonb_sendPGNSPPGUID121000fffftfi1017"3802"_null__null__null__null_jsonb_send_null__null__null_ ));
46554657
DESCR("I/O");
46564658

4659+
DATA(insertOID=3262 (jsonb_strip_nullsPGNSPPGUID121000fffftfi103802"3802"_null__null__null__null_jsonb_strip_nulls_null__null__null_ ));
4660+
DESCR("remove object fields with null values from jsonb");
4661+
46574662
DATA(insertOID=3478 (jsonb_object_fieldPGNSPPGUID121000fffftfi203802"3802 25"_null__null_"{from_json, field_name}"_null_jsonb_object_field_null__null__null_ ));
46584663
DATA(insertOID=3214 (jsonb_object_field_textPGNSPPGUID121000fffftfi2025"3802 25"_null__null_"{from_json, field_name}"_null_jsonb_object_field_text_null__null__null_ ));
46594664
DATA(insertOID=3215 (jsonb_array_elementPGNSPPGUID121000fffftfi203802"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
externDatumjson_populate_recordset(PG_FUNCTION_ARGS);
6464
externDatumjson_to_record(PG_FUNCTION_ARGS);
6565
externDatumjson_to_recordset(PG_FUNCTION_ARGS);
66+
externDatumjson_strip_nulls(PG_FUNCTION_ARGS);
6667

6768
externDatumjsonb_object_field(PG_FUNCTION_ARGS);
6869
externDatumjsonb_object_field_text(PG_FUNCTION_ARGS);
@@ -80,5 +81,6 @@ extern Datum jsonb_populate_record(PG_FUNCTION_ARGS);
8081
externDatumjsonb_populate_recordset(PG_FUNCTION_ARGS);
8182
externDatumjsonb_to_record(PG_FUNCTION_ARGS);
8283
externDatumjsonb_to_recordset(PG_FUNCTION_ARGS);
84+
externDatumjsonb_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)
[8]ページ先頭
©2009-2025 Movatter.jp