jsonb_strip_nulls with arrays?
Currently:
jsonb_strip_nulls ( jsonb ) → jsonb
Deletes all object fields that have null values from the given JSON value, recursively. Null values that are not object fields are untouched.
Null values that are not object fields are untouched.
Can we revisit this and make it work with arrays, too?
Tbh, at first sight that looked like the expected behavior for me.
That is strip nulls from arrays as well.
This has been available since 9.5 and iiuc predates lots of the jsonb array work.
In practice, though, whenever jsonb_build_array is used (especially with jsonpath),
a few nulls do appear in the resulting array most of the times,
Currently, there’s no expressive way to remove this.
We could also have jsonb_array_strip_nulls(jsonb) as well
On 2024-09-17 Tu 5:26 AM, Florents Tselai wrote:
Currently:
|jsonb_strip_nulls| ( |jsonb| ) → |jsonb|
Deletes all object fields that have null values from the given JSON
value, recursively. Null values that are not object fields are untouched.Null values that are not object fields are untouched.
Can we revisit this and make it work with arrays, too?
Tbh, at first sight that looked like the expected behavior for me.
That is strip nulls from arrays as well.
This has been available since 9.5 and iiuc predates lots of the jsonb
array work.
I don't think that's a great idea. Removing an object field which has a
null value shouldn't have any effect on the surrounding data, nor really
any on other operations (If you try to get the value of the missing
field it should give you back null). But removing a null array member
isn't like that at all - unless it's the trailing member of the array it
will renumber all the succeeding array members.
And I don't think we should be changing the behaviour of a function,
that people might have been relying on for the better part of a decade.
In practice, though, whenever jsonb_build_array is used (especially
with jsonpath),a few nulls do appear in the resulting array most of the times,
Currently, there’s no expressive way to remove this.
We could also have jsonb_array_strip_nulls(jsonb) as well
We could, if we're going to do anything at all in this area. Another
possibility would be to provide a second optional parameter for
json{b}_strip_nulls. That's probably a better way to go.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
On Tue, Sep 17, 2024 at 5:11 PM Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-09-17 Tu 5:26 AM, Florents Tselai wrote:
Currently:
jsonb_strip_nulls ( jsonb ) → jsonb
Deletes all object fields that have null values from the given JSON value,
recursively. Null values that are not object fields are untouched.Null values that are not object fields are untouched.
Can we revisit this and make it work with arrays, too?
Tbh, at first sight that looked like the expected behavior for me.
That is strip nulls from arrays as well.
This has been available since 9.5 and iiuc predates lots of the jsonb
array work.I don't think that's a great idea. Removing an object field which has a
null value shouldn't have any effect on the surrounding data, nor really
any on other operations (If you try to get the value of the missing field
it should give you back null). But removing a null array member isn't like
that at all - unless it's the trailing member of the array it will renumber
all the succeeding array members.And I don't think we should be changing the behaviour of a function, that
people might have been relying on for the better part of a decade.In practice, though, whenever jsonb_build_array is used (especially with
jsonpath),a few nulls do appear in the resulting array most of the times,
Currently, there’s no expressive way to remove this.
We could also have jsonb_array_strip_nulls(jsonb) as well
We could, if we're going to do anything at all in this area. Another
possibility would be to provide a second optional parameter for
json{b}_strip_nulls. That's probably a better way to go.
Here's a patch that adds that argument (only for jsonb; no json
implementation yet)
That's how I imagined & implemented it,
but there may be non-obvious pitfalls in the semantics.
as-is version
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)
with the additional boolean flag added
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)
GH PR view: https://github.com/Florents-Tselai/postgres/pull/6/files
Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Attachments:
v1-0002-Add-docs-for-strip_in_arrays-argument.patchapplication/octet-stream; name=v1-0002-Add-docs-for-strip_in_arrays-argument.patchDownload
From a29ac77c075523cf1e885a4b5ade6249b82ec82a Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Tue, 17 Sep 2024 23:38:01 +0300
Subject: [PATCH v1 2/2] Add docs for strip_in_arrays argument
---
doc/src/sgml/func.sgml | 12 +++++++++---
1 file changed, 9 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 84eb3a45ee..5840d833d3 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17119,25 +17119,31 @@ ERROR: value too long for type character(2)
<indexterm>
<primary>json_strip_nulls</primary>
</indexterm>
- <function>json_strip_nulls</function> ( <type>json</type> )
+ <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
<indexterm>
<primary>jsonb_strip_nulls</primary>
</indexterm>
- <function>jsonb_strip_nulls</function> ( <type>jsonb</type> )
+ <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
Deletes all object fields that have null values from the given JSON
value, recursively. Null values that are not object fields are
untouched.
+ If <parameter>strip_in_arrays</parameter> is true (default is false), null array elements are also stripped.
</para>
<para>
<literal>json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')</literal>
<returnvalue>[{"f1":1},2,null,3]</returnvalue>
- </para></entry>
+ </para>
+ <para>
+ <literal>jsonb_strip_nulls('[1,2,null,3,4]', true);</literal>
+ <returnvalue>[1,2,3,4]</returnvalue>
+ </para>
+ </entry>
</row>
<row>
--
2.39.3 (Apple Git-146)
v1-0001-jsonb_strip_nulls-jsonb-bool-wip.patchapplication/octet-stream; name=v1-0001-jsonb_strip_nulls-jsonb-bool-wip.patchDownload
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)
On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:
We could, if we're going to do anything at all in this area.
Another possibility would be to provide a second optional
parameter for json{b}_strip_nulls. That's probably a better way to go.Here's a patch that adds that argument (only for jsonb; no json
implementation yet)
I think it looks sane. We're not stripping a top level null, which is
one thing I looked out for.
I think we need a json implementation as well, though.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:
We could, if we're going to do anything at all in this area. Another possibility would be to provide a second optional parameter for json{b}_strip_nulls. That's probably a better way to go.
Here's a patch that adds that argument (only for jsonb; no json implementation yet)
I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.
I think we need a json implementation as well, though.
Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.
Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>
On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> wrote:
On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:
We could, if we're going to do anything at all in this area. Another possibility would be to provide a second optional parameter for json{b}_strip_nulls. That's probably a better way to go.
Here's a patch that adds that argument (only for jsonb; no json implementation yet)
I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.
I think we need a json implementation as well, though.
Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.
Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.

Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>
Attachments:
v2-0001-jsonb_strip_nulls-jsonb-bool-wip.patchapplication/octet-stream; name=v2-0001-jsonb_strip_nulls-jsonb-bool-wip.patch; x-unix-mode=0644Download
From cd7a43911daeae9af09a5163c95a2c4c3268f497 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 v2 1/3] 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 591157b1d1b..54bf061036f 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -607,6 +607,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 c2e90f1a3bf..cc74fa55a11 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 b37e8a6f882..0a812d6a23b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10174,7 +10174,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 7d163a156e3..e55ed196e84 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 5f0190d5a2b..a6eee0da3cb 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.48.1
v2-0002-Add-docs-for-strip_in_arrays-argument.patchapplication/octet-stream; name=v2-0002-Add-docs-for-strip_in_arrays-argument.patch; x-unix-mode=0644Download
From 9672e714371e7fdf12b0c58fe0b355e2a8d1ebd2 Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Tue, 17 Sep 2024 23:38:01 +0300
Subject: [PATCH v2 2/3] Add docs for strip_in_arrays argument
---
doc/src/sgml/func.sgml | 12 +++++++++---
1 file changed, 9 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 47370e581ae..129103323c5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17179,25 +17179,31 @@ ERROR: value too long for type character(2)
<indexterm>
<primary>json_strip_nulls</primary>
</indexterm>
- <function>json_strip_nulls</function> ( <type>json</type> )
+ <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
<indexterm>
<primary>jsonb_strip_nulls</primary>
</indexterm>
- <function>jsonb_strip_nulls</function> ( <type>jsonb</type> )
+ <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
Deletes all object fields that have null values from the given JSON
value, recursively. Null values that are not object fields are
untouched.
+ If <parameter>strip_in_arrays</parameter> is true (default is false), null array elements are also stripped.
</para>
<para>
<literal>json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')</literal>
<returnvalue>[{"f1":1},2,null,3]</returnvalue>
- </para></entry>
+ </para>
+ <para>
+ <literal>jsonb_strip_nulls('[1,2,null,3,4]', true);</literal>
+ <returnvalue>[1,2,3,4]</returnvalue>
+ </para>
+ </entry>
</row>
<row>
--
2.48.1
v2-0003-Add-implementation-for-json_strip_nulls-json-bool.patchapplication/octet-stream; name=v2-0003-Add-implementation-for-json_strip_nulls-json-bool.patch; x-unix-mode=0644Download
From 42664c0db2032b491b1e9926748d367c421540d0 Mon Sep 17 00:00:00 2001
From: Florents Tselai <florents.tselai@gmail.com>
Date: Wed, 19 Feb 2025 23:12:58 +0200
Subject: [PATCH v2 3/3] Add implementation for json_strip_nulls(json,bool)
---
src/backend/catalog/system_functions.sql | 7 ++++
src/backend/utils/adt/jsonfuncs.c | 16 +++++++-
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/json.out | 50 ++++++++++++++++++++++++
src/test/regress/sql/json.sql | 19 +++++++++
5 files changed, 92 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 54bf061036f..60306e09f0f 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -614,6 +614,13 @@ LANGUAGE INTERNAL
STRICT STABLE PARALLEL SAFE
AS 'jsonb_strip_nulls';
+CREATE OR REPLACE FUNCTION
+ json_strip_nulls(target json, strip_in_arrays boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE PARALLEL SAFE
+AS 'json_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 cc74fa55a11..9f43b58dba5 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -286,6 +286,7 @@ typedef struct StripnullState
JsonLexContext *lex;
StringInfo strval;
bool skip_next_null;
+ bool strip_in_arrays;
} StripnullState;
/* structure for generalized json/jsonb value passing */
@@ -4460,8 +4461,19 @@ sn_array_element_start(void *state, bool isnull)
{
StripnullState *_state = (StripnullState *) state;
- if (_state->strval->data[_state->strval->len - 1] != '[')
+ /* If strip_in_arrays is enabled and this is a null, mark it for skipping */
+ if (isnull && _state->strip_in_arrays)
+ {
+ _state->skip_next_null = true;
+ return JSON_SUCCESS;
+ }
+
+ /* Only add a comma if this is not the first valid element */
+ if (_state->strval->len > 0 &&
+ _state->strval->data[_state->strval->len - 1] != '[')
+ {
appendStringInfoCharMacro(_state->strval, ',');
+ }
return JSON_SUCCESS;
}
@@ -4493,6 +4505,7 @@ Datum
json_strip_nulls(PG_FUNCTION_ARGS)
{
text *json = PG_GETARG_TEXT_PP(0);
+ bool strip_in_arrays = PG_NARGS() == 2 ? PG_GETARG_BOOL(1) : false;
StripnullState *state;
JsonLexContext lex;
JsonSemAction *sem;
@@ -4503,6 +4516,7 @@ json_strip_nulls(PG_FUNCTION_ARGS)
state->lex = makeJsonLexContext(&lex, json, true);
state->strval = makeStringInfo();
state->skip_next_null = false;
+ state->strip_in_arrays = strip_in_arrays;
sem->semstate = state;
sem->object_start = sn_object_start;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0a812d6a23b..d0ebb4b8541 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9238,7 +9238,7 @@
proname => 'to_json', provolatile => 's', prorettype => 'json',
proargtypes => 'anyelement', prosrc => 'to_json' },
{ oid => '3261', descr => 'remove object fields with null values from json',
- proname => 'json_strip_nulls', prorettype => 'json', proargtypes => 'json',
+ proname => 'json_strip_nulls', prorettype => 'json', proargtypes => 'json bool',
prosrc => 'json_strip_nulls' },
{ oid => '3947',
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 96c40911cb9..04b478cb468 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -2504,6 +2504,56 @@ select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
{"a":{},"d":{}}
(1 row)
+-- json_strip_nulls (strip_in_arrays=true)
+select json_strip_nulls(null, true);
+ json_strip_nulls
+------------------
+
+(1 row)
+
+select json_strip_nulls('1', true);
+ json_strip_nulls
+------------------
+ 1
+(1 row)
+
+select json_strip_nulls('"a string"', true);
+ json_strip_nulls
+------------------
+ "a string"
+(1 row)
+
+select json_strip_nulls('null', true);
+ json_strip_nulls
+------------------
+ null
+(1 row)
+
+select json_strip_nulls('[1,2,null,3,4]', true);
+ json_strip_nulls
+------------------
+ [1,2,3,4]
+(1 row)
+
+select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
+ json_strip_nulls
+-------------------------------
+ {"a":1,"c":[2,3],"d":{"e":4}}
+(1 row)
+
+select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
+ json_strip_nulls
+---------------------
+ [1,{"a":1,"c":2},3]
+(1 row)
+
+-- an empty object is not null and should not be stripped
+select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
+ json_strip_nulls
+------------------
+ {"a":{},"d":{}}
+(1 row)
+
-- json to tsvector
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json);
to_tsvector
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 8251f4f4006..e9800b21ffe 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -814,6 +814,25 @@ select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]');
-- an empty object is not null and should not be stripped
select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }');
+-- json_strip_nulls (strip_in_arrays=true)
+
+select json_strip_nulls(null, true);
+
+select json_strip_nulls('1', true);
+
+select json_strip_nulls('"a string"', true);
+
+select json_strip_nulls('null', true);
+
+select json_strip_nulls('[1,2,null,3,4]', true);
+
+select json_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}', true);
+
+select json_strip_nulls('[1,{"a":1,"b":null,"c":2},3]', true);
+
+-- an empty object is not null and should not be stripped
+select json_strip_nulls('{"a": {"b": null, "c": null}, "d": {} }', true);
+
-- json to tsvector
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::json);
--
2.48.1
On 2025-02-19 We 4:23 PM, Florents Tselai wrote:
On 18 Jan 2025, at 11:51 AM, Florents Tselai
<florents.tselai@gmail.com> wrote:On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:
We could, if we're going to do anything at all in this area.
Another possibility would be to provide a second optional
parameter for json{b}_strip_nulls. That's probably a better way
to go.Here's a patch that adds that argument (only for jsonb; no json
implementation yet)I think it looks sane. We're not stripping a top level null, which
is one thing I looked out for.I think we need a json implementation as well, though.
Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json
implementation too.Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.
Please add this to the next Commitfest at
https://commitfest.postgresql.org/52/
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
On 20 Feb 2025, at 12:18 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
On 2025-02-19 We 4:23 PM, Florents Tselai wrote:
On 18 Jan 2025, at 11:51 AM, Florents Tselai <florents.tselai@gmail.com> <mailto:florents.tselai@gmail.com> wrote:
On 8 Jan 2025, at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> <mailto:andrew@dunslane.net> wrote:
On 2024-09-17 Tu 4:53 PM, Florents Tselai wrote:
We could, if we're going to do anything at all in this area. Another possibility would be to provide a second optional parameter for json{b}_strip_nulls. That's probably a better way to go.
Here's a patch that adds that argument (only for jsonb; no json implementation yet)
I think it looks sane. We're not stripping a top level null, which is one thing I looked out for.
I think we need a json implementation as well, though.
Thanks for having a Look, Andrew;
if there aren’t any other objections, I’ll come back with a json implementation too.Attached is a v2 patch with the missing json implementation.
jsonb one remains the same.Please add this to the next Commitfest at https://commitfest.postgresql.org/52/
Added ; thanks
https://commitfest.postgresql.org/patch/5260/
Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com <https://www.enterprisedb.com/>
Hi
2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:
Please add this to the next Commitfest at https://commitfest.postgresql.org/52/
Added ; thanks
https://commitfest.postgresql.org/patch/5260/
I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.
Regards
Ian Barwick
Attachments:
json_strip_nulls-doc-fix.patchtext/x-patch; charset=US-ASCII; name=json_strip_nulls-doc-fix.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f97f0ce570a..e5a3c3427ec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17345,14 +17345,14 @@ ERROR: value too long for type character(2)
<indexterm>
<primary>json_strip_nulls</primary>
</indexterm>
- <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
+ <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type> <optional>, <parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
<indexterm>
<primary>jsonb_strip_nulls</primary>
</indexterm>
- <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type>, <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
+ <function>jsonb_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type> <optional>, <parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
On 6 Mar 2025, at 2:10 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
Hi
2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:
Please add this to the next Commitfest at https://commitfest.postgresql.org/52/
Added ; thanks
https://commitfest.postgresql.org/patch/5260/I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.Regards
Ian Barwick
<json_strip_nulls-doc-fix.patch>
You’re corrrect.
On 2025-03-05 We 7:10 PM, Ian Lawrence Barwick wrote:
Hi
2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:
Please add this to the next Commitfest at https://commitfest.postgresql.org/52/
Added ; thanks
https://commitfest.postgresql.org/patch/5260/I see this was committed, but there's a small formatting error in the docs
(extra comma in the parameter list); patch attached.
Thanks, pushed.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi,
Thanks for developing the good feature.
I've attached a small patch for the documentation of the json_strip_nulls function. The data type of the 'target' parameter is different between the implementation and the documentation. The implementation is json_stripe_nulls (target JSON, ...), but the current documentation says json_stripe_nulls(target JSONB, ...).
Regards,
Noriyoshi Shinoda
-----Original Message-----
From: Andrew Dunstan <andrew@dunslane.net>
Sent: Thursday, March 6, 2025 10:51 PM
To: Ian Lawrence Barwick <barwick@gmail.com>; Florents Tselai <florents.tselai@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: jsonb_strip_nulls with arrays?
On 2025-03-05 We 7:10 PM, Ian Lawrence Barwick wrote:
Hi
2025年3月1日(土) 2:58 Florents Tselai <florents.tselai@gmail.com>:
Please add this to the next Commitfest at
https://urldefense.com/v3/__https://commitfest.postgresql.org/52/__;!
!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsyF4IeybO-
t--xu2idPkLF240-sxqpR$Added ; thanks
https://urldefense.com/v3/__https://commitfest.postgresql.org/patch/5
260/__;!!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsy
F4IeybO-t--xu2idPkLF24zKVAj03$I see this was committed, but there's a small formatting error in the
docs (extra comma in the parameter list); patch attached.
Thanks, pushed.
cheers
andrew
--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!NpxR!moyYqgSNJn8nLYGjlKKZDuERAzIwnFY8Ge_C5MHDuoPRpTJI9Ee0gsyF4IeybO-t--xu2idPkLF24-WSxzSS$
Attachments:
json_strip_nulls_doc_v1.diffapplication/octet-stream; name=json_strip_nulls_doc_v1.diffDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 53565075ca7..4d6061a8458 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17345,7 +17345,7 @@ ERROR: value too long for type character(2)
<indexterm>
<primary>json_strip_nulls</primary>
</indexterm>
- <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>jsonb</type> <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
+ <function>json_strip_nulls</function> ( <parameter>target</parameter> <type>json</type> <optional>,<parameter>strip_in_arrays</parameter> <type>boolean</type> </optional> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
On 2025-03-06 Th 9:17 AM, Shinoda, Noriyoshi (SXD Japan FSI) wrote:
Hi,
Thanks for developing the good feature.
I've attached a small patch for the documentation of the json_strip_nulls function. The data type of the 'target' parameter is different between the implementation and the documentation. The implementation is json_stripe_nulls (target JSON, ...), but the current documentation says json_stripe_nulls(target JSONB, ...).
Argh! My glasses must have been fogged up yesterday.
pushed, thanks
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com