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

Commit4603903

Browse files
committed
Allow json{b}_strip_nulls to remove null array elements
An additional paramater ("strip_in_arrays") is added to these functions.It defaults to false. If true, then null array elements are removed aswell as null valued object fields. JSON that just consists of a singlenull is not affected.Author: Florents Tselai <florents.tselai@gmail.com>Discussion:https://postgr.es/m/4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com
1 parent5ead85f commit4603903

File tree

8 files changed

+190
-9
lines changed

8 files changed

+190
-9
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -17345,25 +17345,32 @@ ERROR: value too long for type character(2)
1734517345
<indexterm>
1734617346
<primary>json_strip_nulls</primary>
1734717347
</indexterm>
17348-
<function>json_strip_nulls</function> ( <type>json</type> )
17348+
<function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
1734917349
<returnvalue>json</returnvalue>
1735017350
</para>
1735117351
<para role="func_signature">
1735217352
<indexterm>
1735317353
<primary>jsonb_strip_nulls</primary>
1735417354
</indexterm>
17355-
<function>jsonb_strip_nulls</function> ( <type>jsonb</type> )
17355+
<function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
1735617356
<returnvalue>jsonb</returnvalue>
1735717357
</para>
1735817358
<para>
1735917359
Deletes all object fields that have null values from the given JSON
17360-
value, recursively. Null values that are not object fields are
17361-
untouched.
17360+
value, recursively.
17361+
If <parameter>strip_in_arrays</parameter> is true (the default is false),
17362+
null array elements are also stripped.
17363+
Otherwise they are not stripped. Bare null values are never stripped.
1736217364
</para>
1736317365
<para>
1736417366
<literal>json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')</literal>
1736517367
<returnvalue>[{"f1":1},2,null,3]</returnvalue>
17366-
</para></entry>
17368+
</para>
17369+
<para>
17370+
<literal>jsonb_strip_nulls('[1,2,null,3,4]', true);</literal>
17371+
<returnvalue>[1,2,3,4]</returnvalue>
17372+
</para>
17373+
</entry>
1736717374
</row>
1736817375

1736917376
<row>

‎src/backend/catalog/system_functions.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -607,6 +607,20 @@ LANGUAGE INTERNAL
607607
STRICT STABLE PARALLEL SAFE
608608
AS'jsonb_path_query_first_tz';
609609

610+
CREATEOR REPLACE FUNCTION
611+
jsonb_strip_nulls(target jsonb, strip_in_arraysboolean DEFAULT false)
612+
RETURNS jsonb
613+
LANGUAGE INTERNAL
614+
STRICT STABLE PARALLEL SAFE
615+
AS'jsonb_strip_nulls';
616+
617+
CREATEOR REPLACE FUNCTION
618+
json_strip_nulls(target json, strip_in_arraysboolean DEFAULT false)
619+
RETURNS json
620+
LANGUAGE INTERNAL
621+
STRICT STABLE PARALLEL SAFE
622+
AS'json_strip_nulls';
623+
610624
-- default normalization form is NFC, per SQL standard
611625
CREATEOR REPLACE FUNCTION
612626
"normalize"(text,text DEFAULT'NFC')

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

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,7 @@ typedef struct StripnullState
286286
JsonLexContext*lex;
287287
StringInfostrval;
288288
boolskip_next_null;
289+
boolstrip_in_arrays;
289290
}StripnullState;
290291

291292
/* structure for generalized json/jsonb value passing */
@@ -4460,8 +4461,19 @@ sn_array_element_start(void *state, bool isnull)
44604461
{
44614462
StripnullState*_state= (StripnullState*)state;
44624463

4463-
if (_state->strval->data[_state->strval->len-1]!='[')
4464+
/* If strip_in_arrays is enabled and this is a null, mark it for skipping */
4465+
if (isnull&&_state->strip_in_arrays)
4466+
{
4467+
_state->skip_next_null= true;
4468+
returnJSON_SUCCESS;
4469+
}
4470+
4471+
/* Only add a comma if this is not the first valid element */
4472+
if (_state->strval->len>0&&
4473+
_state->strval->data[_state->strval->len-1]!='[')
4474+
{
44644475
appendStringInfoCharMacro(_state->strval,',');
4476+
}
44654477

44664478
returnJSON_SUCCESS;
44674479
}
@@ -4493,6 +4505,7 @@ Datum
44934505
json_strip_nulls(PG_FUNCTION_ARGS)
44944506
{
44954507
text*json=PG_GETARG_TEXT_PP(0);
4508+
boolstrip_in_arrays=PG_NARGS()==2 ?PG_GETARG_BOOL(1) : false;
44964509
StripnullState*state;
44974510
JsonLexContextlex;
44984511
JsonSemAction*sem;
@@ -4503,6 +4516,7 @@ json_strip_nulls(PG_FUNCTION_ARGS)
45034516
state->lex=makeJsonLexContext(&lex,json, true);
45044517
state->strval=makeStringInfo();
45054518
state->skip_next_null= false;
4519+
state->strip_in_arrays=strip_in_arrays;
45064520

45074521
sem->semstate=state;
45084522
sem->object_start=sn_object_start;
@@ -4520,12 +4534,13 @@ json_strip_nulls(PG_FUNCTION_ARGS)
45204534
}
45214535

45224536
/*
4523-
* SQL function jsonb_strip_nulls(jsonb) -> jsonb
4537+
* SQL function jsonb_strip_nulls(jsonb, bool) -> jsonb
45244538
*/
45254539
Datum
45264540
jsonb_strip_nulls(PG_FUNCTION_ARGS)
45274541
{
45284542
Jsonb*jb=PG_GETARG_JSONB_P(0);
4543+
boolstrip_in_arrays= false;
45294544
JsonbIterator*it;
45304545
JsonbParseState*parseState=NULL;
45314546
JsonbValue*res=NULL;
@@ -4534,6 +4549,9 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
45344549
JsonbIteratorTokentype;
45354550
boollast_was_key= false;
45364551

4552+
if (PG_NARGS()==2)
4553+
strip_in_arrays=PG_GETARG_BOOL(1);
4554+
45374555
if (JB_ROOT_IS_SCALAR(jb))
45384556
PG_RETURN_POINTER(jb);
45394557

@@ -4564,6 +4582,11 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
45644582
(void)pushJsonbValue(&parseState,WJB_KEY,&k);
45654583
}
45664584

4585+
/* if strip_in_arrays is set, also skip null array elements */
4586+
if (strip_in_arrays)
4587+
if (type==WJB_ELEM&&v.type==jbvNull)
4588+
continue;
4589+
45674590
if (type==WJB_VALUE||type==WJB_ELEM)
45684591
res=pushJsonbValue(&parseState,type,&v);
45694592
else

‎src/include/catalog/pg_proc.dat

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9269,7 +9269,7 @@
92699269
proname => 'to_json', provolatile => 's', prorettype => 'json',
92709270
proargtypes => 'anyelement', prosrc => 'to_json' },
92719271
{ oid => '3261', descr => 'remove object fields with null values from json',
9272-
proname => 'json_strip_nulls', prorettype => 'json', proargtypes => 'json',
9272+
proname => 'json_strip_nulls', prorettype => 'json', proargtypes => 'json bool',
92739273
prosrc => 'json_strip_nulls' },
92749274

92759275
{ oid => '3947',
@@ -10205,7 +10205,7 @@
1020510205
prorettype => 'jsonb', proargtypes => '',
1020610206
prosrc => 'jsonb_build_object_noargs' },
1020710207
{ oid => '3262', descr => 'remove object fields with null values from jsonb',
10208-
proname => 'jsonb_strip_nulls', prorettype => 'jsonb', proargtypes => 'jsonb',
10208+
proname => 'jsonb_strip_nulls', prorettype => 'jsonb', proargtypes => 'jsonb bool',
1020910209
prosrc => 'jsonb_strip_nulls' },
1021010210

1021110211
{ oid => '3478',

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2504,6 +2504,56 @@ select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
25042504
{"a":{},"d":{}}
25052505
(1 row)
25062506

2507+
-- json_strip_nulls (strip_in_arrays=true)
2508+
select json_strip_nulls(null, true);
2509+
json_strip_nulls
2510+
------------------
2511+
2512+
(1 row)
2513+
2514+
select json_strip_nulls('1', true);
2515+
json_strip_nulls
2516+
------------------
2517+
1
2518+
(1 row)
2519+
2520+
select json_strip_nulls('"a string"', true);
2521+
json_strip_nulls
2522+
------------------
2523+
"a string"
2524+
(1 row)
2525+
2526+
select json_strip_nulls('null', true);
2527+
json_strip_nulls
2528+
------------------
2529+
null
2530+
(1 row)
2531+
2532+
select json_strip_nulls('[1,2,null,3,4]', true);
2533+
json_strip_nulls
2534+
------------------
2535+
[1,2,3,4]
2536+
(1 row)
2537+
2538+
select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
2539+
json_strip_nulls
2540+
-------------------------------
2541+
{"a":1,"c":[2,3],"d":{"e":4}}
2542+
(1 row)
2543+
2544+
select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
2545+
json_strip_nulls
2546+
---------------------
2547+
[1,{"a":1,"c":2},3]
2548+
(1 row)
2549+
2550+
-- an empty object is not null and should not be stripped
2551+
select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
2552+
json_strip_nulls
2553+
------------------
2554+
{"a":{},"d":{}}
2555+
(1 row)
2556+
25072557
-- json to tsvector
25082558
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json);
25092559
to_tsvector

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4153,6 +4153,56 @@ select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
41534153
{"a": {}, "d": {}}
41544154
(1 row)
41554155

4156+
-- jsonb_strip_nulls (strip_in_arrays=true)
4157+
select jsonb_strip_nulls(null, true);
4158+
jsonb_strip_nulls
4159+
-------------------
4160+
4161+
(1 row)
4162+
4163+
select jsonb_strip_nulls('1', true);
4164+
jsonb_strip_nulls
4165+
-------------------
4166+
1
4167+
(1 row)
4168+
4169+
select jsonb_strip_nulls('"a string"', true);
4170+
jsonb_strip_nulls
4171+
-------------------
4172+
"a string"
4173+
(1 row)
4174+
4175+
select jsonb_strip_nulls('null', true);
4176+
jsonb_strip_nulls
4177+
-------------------
4178+
null
4179+
(1 row)
4180+
4181+
select jsonb_strip_nulls('[1,2,null,3,4]', true);
4182+
jsonb_strip_nulls
4183+
-------------------
4184+
[1, 2, 3, 4]
4185+
(1 row)
4186+
4187+
select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
4188+
jsonb_strip_nulls
4189+
--------------------------------------
4190+
{"a": 1, "c": [2, 3], "d": {"e": 4}}
4191+
(1 row)
4192+
4193+
select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
4194+
jsonb_strip_nulls
4195+
--------------------------
4196+
[1, {"a": 1, "c": 2}, 3]
4197+
(1 row)
4198+
4199+
-- an empty object is not null and should not be stripped
4200+
select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
4201+
jsonb_strip_nulls
4202+
--------------------
4203+
{"a": {}, "d": {}}
4204+
(1 row)
4205+
41564206
select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
41574207
jsonb_pretty
41584208
----------------------------

‎src/test/regress/sql/json.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -814,6 +814,25 @@ select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
814814
-- an empty object is not null and should not be stripped
815815
select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
816816

817+
-- json_strip_nulls (strip_in_arrays=true)
818+
819+
select json_strip_nulls(null, true);
820+
821+
select json_strip_nulls('1', true);
822+
823+
select json_strip_nulls('"a string"', true);
824+
825+
select json_strip_nulls('null', true);
826+
827+
select json_strip_nulls('[1,2,null,3,4]', true);
828+
829+
select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
830+
831+
select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
832+
833+
-- an empty object is not null and should not be stripped
834+
select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
835+
817836
-- json to tsvector
818837
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json);
819838

‎src/test/regress/sql/jsonb.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1102,6 +1102,24 @@ select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
11021102
-- an empty object is not null and should not be stripped
11031103
select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
11041104

1105+
-- jsonb_strip_nulls (strip_in_arrays=true)
1106+
1107+
select jsonb_strip_nulls(null, true);
1108+
1109+
select jsonb_strip_nulls('1', true);
1110+
1111+
select jsonb_strip_nulls('"a string"', true);
1112+
1113+
select jsonb_strip_nulls('null', true);
1114+
1115+
select jsonb_strip_nulls('[1,2,null,3,4]', true);
1116+
1117+
select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
1118+
1119+
select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
1120+
1121+
-- an empty object is not null and should not be stripped
1122+
select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
11051123

11061124
select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
11071125
select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp