v1-0001-jsonb_strip_nulls-jsonb-bool-wip.patch

From 86e8883459fa3e4f64c1c16a31b49d123aac472f Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Tue, 17 Sep 2024 22:05:58 +0300
Subject: [PATCH v1 1/2] jsonb_strip_nulls(jsonb, bool) wip

Implementation and passing tests for jsonb_strip_nulls(jsonb, bool)
no docs yet.
---
 src/backend/catalog/system_functions.sql |  7 ++++
 src/backend/utils/adt/jsonfuncs.c        | 11 +++++-
 src/include/catalog/pg_proc.dat          |  2 +-
 src/test/regress/expected/jsonb.out      | 50 ++++++++++++++++++++++++
 src/test/regress/sql/jsonb.sql           | 18 +++++++++
 5 files changed, 86 insertions(+), 2 deletions(-)

diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 623b9539b1..2e9fcc9e02 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -610,6 +610,13 @@ LANGUAGE INTERNAL
 STRICT STABLE PARALLEL SAFE
 AS 'jsonb_path_query_first_tz';
 
+CREATE OR REPLACE FUNCTION
+  jsonb_strip_nulls(target jsonb, strip_in_arrays boolean DEFAULT false)
+RETURNS jsonb
+LANGUAGE INTERNAL
+STRICT STABLE PARALLEL SAFE
+AS 'jsonb_strip_nulls';
+
 -- default normalization form is NFC, per SQL standard
 CREATE OR REPLACE FUNCTION
   "normalize"(text, text DEFAULT 'NFC')
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 62a17a2667..7d6b4ebfaa 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -4520,12 +4520,13 @@ json_strip_nulls(PG_FUNCTION_ARGS)
 }
 
 /*
- * SQL function jsonb_strip_nulls(jsonb) -> jsonb
+ * SQL function jsonb_strip_nulls(jsonb, bool) -> jsonb
  */
 Datum
 jsonb_strip_nulls(PG_FUNCTION_ARGS)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	bool		strip_in_arrays = false;
 	JsonbIterator *it;
 	JsonbParseState *parseState = NULL;
 	JsonbValue *res = NULL;
@@ -4534,6 +4535,9 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
 	JsonbIteratorToken type;
 	bool		last_was_key = false;
 
+	if (PG_NARGS() == 2)
+		strip_in_arrays = PG_GETARG_BOOL(1);
+
 	if (JB_ROOT_IS_SCALAR(jb))
 		PG_RETURN_POINTER(jb);
 
@@ -4564,6 +4568,11 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
 			(void) pushJsonbValue(&parseState, WJB_KEY, &k);
 		}
 
+		/* if strip_in_arrays is set, also skip null array elements */
+		if (strip_in_arrays)
+			if (type == WJB_ELEM && v.type == jbvNull)
+				continue;
+
 		if (type == WJB_VALUE || type == WJB_ELEM)
 			res = pushJsonbValue(&parseState, type, &v);
 		else
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2513c36fcb..03ad446c76 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10114,7 +10114,7 @@
   prorettype => 'jsonb', proargtypes => '',
   prosrc => 'jsonb_build_object_noargs' },
 { oid => '3262', descr => 'remove object fields with null values from jsonb',
-  proname => 'jsonb_strip_nulls', prorettype => 'jsonb', proargtypes => 'jsonb',
+  proname => 'jsonb_strip_nulls', prorettype => 'jsonb', proargtypes => 'jsonb bool',
   prosrc => 'jsonb_strip_nulls' },
 
 { oid => '3478',
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..e55ed196e8 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4153,6 +4153,56 @@ select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
  {"a": {}, "d": {}}
 (1 row)
 
+-- jsonb_strip_nulls (strip_in_arrays=true)
+select jsonb_strip_nulls(null, true);
+ jsonb_strip_nulls 
+-------------------
+ 
+(1 row)
+
+select jsonb_strip_nulls('1', true);
+ jsonb_strip_nulls 
+-------------------
+ 1
+(1 row)
+
+select jsonb_strip_nulls('"a string"', true);
+ jsonb_strip_nulls 
+-------------------
+ "a string"
+(1 row)
+
+select jsonb_strip_nulls('null', true);
+ jsonb_strip_nulls 
+-------------------
+ null
+(1 row)
+
+select jsonb_strip_nulls('[1,2,null,3,4]', true);
+ jsonb_strip_nulls 
+-------------------
+ [1, 2, 3, 4]
+(1 row)
+
+select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
+          jsonb_strip_nulls           
+--------------------------------------
+ {"a": 1, "c": [2, 3], "d": {"e": 4}}
+(1 row)
+
+select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
+    jsonb_strip_nulls     
+--------------------------
+ [1, {"a": 1, "c": 2}, 3]
+(1 row)
+
+-- an empty object is not null and should not be stripped
+select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
+ jsonb_strip_nulls  
+--------------------
+ {"a": {}, "d": {}}
+(1 row)
+
 select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
         jsonb_pretty        
 ----------------------------
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..a6eee0da3c 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1102,6 +1102,24 @@ select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
 -- an empty object is not null and should not be stripped
 select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
 
+-- jsonb_strip_nulls (strip_in_arrays=true)
+
+select jsonb_strip_nulls(null, true);
+
+select jsonb_strip_nulls('1', true);
+
+select jsonb_strip_nulls('"a string"', true);
+
+select jsonb_strip_nulls('null', true);
+
+select jsonb_strip_nulls('[1,2,null,3,4]', true);
+
+select jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
+
+select jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
+
+-- an empty object is not null and should not be stripped
+select jsonb_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
 
 select jsonb_pretty('{"a": "test", "b": [1, 2, 3], "c": "test3", "d":{"dd": "test4", "dd2":{"ddd": "test5"}}}');
 select jsonb_pretty('[{"f1":1,"f2":null},2,null,[[{"x":true},6,7],8],3]');
-- 
2.39.3 (Apple Git-146)